Introduction

Record Filters are used to reduce the number of records shown in the plots. By reducing the number of records you can group parts made within a time frame or by machine number, cavity number, operator, or any other Trace Fields. This session discusses the manual and automatic methods of reducing data helping you quickly filter down to relevant data or identify problems.

Displaying Record Filters

The Record Filter panel is displayed by selecting the View > Record Filter menu or clicking the button. The top of the list may contain predefined filters denoted in parentheses and are not configurable.

The (Show All) is the default filter; selecting it removes any active filter and makes all records visible.

Using Record Filters

Simply click on a Record Filter to apply its effects.

By default, several date oriented filters are provided to help you quickly reduce the viewed data to THIS_WEEK or LAST_WEEK. Using these built-in filters reduces the time spent creating filter of exact dates. As these are dynamic filters, the result of these filters changes each week as time passes.

Creating a Record Filter

You can create your own record filter by clicking on the button which prompts the Record Filter Editor to display. Be sure to give is a name in the Filter Name box at the top before beginning. You should use very clear names on all filters since these names appear in the printed reports making it easy to identify the conditions when the report was created. You can use any characters in your filter name.

Criteria

Criteria give your filter the characteristics it needs to filter down to the desired data. By default, there will only be one line of criteria when you create a new record filter. You can quickly Add and Remove Criteria using the buttons at the top of this menu.

You can change the Filter On setting to be one of many things, such as ‘Record Number’, Dates and Times, ‘Number of Fails’ and many more. You can then use the Comparison clause to decide how this option compares to the Criteria’s value. In other words, you will choose what/when, how and how many to filter.

When using multiple lines, you will need to decide how they relate to one another. Adjust the AND/OR value to decide whether or not your data needs to pass all criteria or any criteria.

To group criteria, you can use the ( parentheses ) boxes to set the nesting of the criteria using one, two or three parenthesis.

Saving the Record Filter

Once you’ve completed creating your record filter click OK to save it; the new filter should immediately appear in the Record Filter panel. The filter will not automatically be applied, so you will want to click on it to review the filtered data to be sure that the criteria is correct.

Edit the Record Filter

When a configurable filter is selected, the pencil icon will be enabled. Clicking on the pencil causes the Record Filter Editor to be displayed again; you can change what you need in the same way you created the filter.

Delete Record Filters

Similar to the Edit button, the trashcan is also enabled and clicking it will delete the currently selected filter. A prompt will ask you to confirm your selection.

1. When using filters, if no data matches your selected filter criteria all the plots will disappear, and you will see a message stating “No records passed the record filter criteria” where the plots would normally be.

Filter Examples:

The following set of examples is supplied to increase your understanding of the QC-CALC filter use.

Example #1

Show only the last 20 records:

Example #2

Show only records received today:

Example #3

Assuming you have multiple machines collecting data to the same file and storing the machine ID in Numeric Trace Field 2, show only parts collected from the machine that sent the last part.

Example #4

Show parts from this week that had at least one characteristic out of spec.

Example #5

Show parts measured last month, on the 2nd shift (between 3pm and 10pm), on machines 1 or 3, measured by Bob or John.

Example #6

Show parts measured by operators other than Bob, which had at least 1 characteristic out of spec and did not have an Assignable Cause or Corrective Action assigned to them:

General Filter Settings

The Tools > Options > Filter is used to control the filtering settings for QC-CALC.

Filter Settings

Start of Week

QC-CALC has built-in filters such as TODAY, THIS-WEEK, LAST-WEEK, LAST-YEAR, etc. to help you with date related filters. This type of filter returns the correct date without having to know the exact dates for the week, month, or year and produces variable results allowing you to reuse a filter easily. Since the first weekday can be defined differently, you have the option of choosing which day begins your week.

Filter Association

Record and Characteristic Filters can be stored and accessed globally (meaning the same filters will be available for all part files) or on a part file basis (each part file would have its own set of filters). Characteristic Filters default to being on a part file level because they are based on characteristic locations within the file and tend to not apply to other part files. Record Filters default to being available globally since it is likely that all of your part files will be set up with a “Lot Number” or “Job Number” Trace Field that is being used in a filter. Checking the Associate ____ Filter with a File or Group option associates that filter type with the part files. Unchecking this option makes that filter type available for all part files.

Filter Window Toolbars

Record Filters

The options shown in this area control the buttons in the Record panels. If the Allow Add/Edit/Delete option is unchecked the corresponding buttons in the panel will be disabled as shown to the right.

Characteristic

The options shown in this area control the buttons in the Characteristic Filter panels. If the Allow Add/Edit/Delete option is unchecked the corresponding buttons in the panel will be disabled as shown to the right.

Filter List Items

This option lets you control the number of Record or Characteristic Filters displayed in the panels. The default value for this setting is 50.

Advanced Filtering

Advanced Mode

The Show Advanced button expands the Record Filter window to include several more options giving you more freedom and customization to your filter. When the Filtering screen is first shown the Simple mode will be displayed. The screen will expand revealing several more options for filtering including an expanded list of fields, and custom trace field lists.

Tip: When creating filters, it helps to complete the sentence, “Show me the data where…” and then enter your criteria. For example, “Show me the data where the date is last week, and the cavity is 4.” In this case, you have two criteria: date = last week AND cavity = 4.

Filter On Explained

Record Number

Using this filter type lets you filter based on the record numbers. The values for this section will be numbers.

Measure Date/Time

Using this filter type lets you specify the time of day and the date that the part was received. This area combines the aspect of the Date and Time Only sections and is used when both the time and the date need to be filtered. The values for this section will be a date.

Measure Time Only

Using this filter type lets you specify the time of day the part was received. The values for this section will be a time of day.

Measure Date Only

Using this filter type allows you to ignore the time portion associated with a date. The values for this section will be a date.

Number of Failures (Current Model)

Each record stored in the part file of the current model has a failure count saved with it. This filter type allows you to filter on the number of failed characteristics within the current model of a part. The values for this section will be numbers. For example, you can build a filter that only reports the parts containing bad characteristics. The filter would read: Number of Fails is > 0

Number of Failures

Each record stored in the part file has a failure count saved with it. This filter type allows you to filter on the number of failed characteristics within a part. The values for this section will be numbers. For example, you can build a filter that only reports the parts containing bad characteristics. The filter would read: Number of Fails is > 0

Number Outside Saved Control Limits

This filter type allows you gather only records where at least one feature was outside the control limits. The values for this section will be numbers.

Assignable Cause/Corrective Action Desc (Description), Ref (Reference)

These filter types allow you to specify whether any features in a record have an Assignable Cause or a Corrective Action associated with it. Either the reference or description field can be filtered. The values for this section will be text.

Numeric Trace Fields

Each part saved in the database has Trace Fields saved with it. This filter type lets you specify the value a Numeric Trace Field equals to satisfy the criteria. There are two special keywords to discuss for the Numeric Trace Field and they are:

Latest Value

Only parts having the same Numeric Trace Field as the most recently received part satisfy this criterion. This filter type is very important since it allows for dynamic filtering. See details below in the Latest Value Filter.

To activate the filter, click on the name and a dialog box asks for the Cavity number and your data is filtered using that value. This area includes dependent and independent values which are further described in that section.

Text Trace Field

The Text Trace Fields are the same as the Numeric Trace Fields, but they allow you to enter text as a value. Please read the definition on Numeric Trace Fields to gain an understanding of Text Trace Field Filter use. This area includes dependent and independent values which are further described in that section.

Characteristics

You can specify the value of any characteristic in the database by selecting the characteristic number. You then specify the allowable range for the characteristic. You might use this to find all parts within the last 100 inspected parts with a characteristic exceeding a particular value.

Ask Filters

This is an option for the criteria section that is listed under criteria with several of the filter types.

When filtering by Numeric and Text Trace fields, the filter criteria field is actually a list much like in QC-CALC. One of the items in the list is “Ask for value”.

When this criterion is used, the filter “value” is requested by a popup window when they click on the filter or at run time.

Upon specifying a value in the popup, the data is then filtered by that value. This is a useful feature because it allows you to re-use the same filter for multiple scenarios.

You can also have multiple ASK criteria sets in each filter giving you a lot of flexibility. For instance:

WHERE Serial Number is BETWEEN ASK and ASK

AND Operator is ASK

AND Machine is greater than or equal to ASK

In the example above, the ASK popup will have space available to enter the answers for all Ask values before the filter is run. Checking the Skip box on the right will cause that portion of the filter to not be used at all.

Data Type Awareness

The Record Filter screen automatically knows the types of the data being queried and loads the correct Comparison operators according to the data type chosen in the Filter On list. For example, if you choose a numeric type such as “Record Number”, you will see numeric type Comparisons such as “is greater than”, “is less than”, etc. If you choose a text type field such as “Employee”, you will see text type Comparisons such as “contains”, “begins with”, etc. For dates, please see the section on Dynamic Date Filtering on page 100.

Trace field Filtering

When filtering using a trace field such as Cavity, the position of the trace field does not matter. The filter will automatically find it and filter it correctly. Therefore, if one of your Part files has Cavity as the 5th numeric trace field and another Part file has it as the 2nd numeric trace field; the filter will work for both Part files.

Uses of AND/OR

The AND/OR option allows you to specify whether all the criteria in your filter need to be met by the data you are filtering, or just certain parts of it. For example, you could create a filter that looks for measurements with a Measure Date of any day after 9/01/2008 and a Record Number greater than 5. If AND is used, all conditions must be met in order for the record (part) to be returned. If OR is used, either of the conditions may be met in order for the record to be returned.

Example: Your database contains records 1-10:

• If you have criteria specifying record number greater than 8 OR less than 2, then you will get record numbers 1, 2, 9, and 10.
• If you have criteria specifying record number greater than 8 AND less than 2, then you will get no records returned. This is because the record number cannot be both greater than 8 and less than 2 at the same time.

Uses of Parentheses

Parentheses allow for even more specific and complex filters, allowing you to group several different criteria as one set. The following example illustrates the use of parentheses.

Example:

You have 3 criteria; date = last week AND cavity = 4 OR cavity = 2.

If you put parentheses around the first two criteria:

(date = last week AND cavity = 4) OR cavity = 2

...you will get all cavity 4 data for last week and then all cavity 2 data regardless of date.

If you put parentheses around the last two criteria:

date = last week AND (cavity = 4 OR cavity = 2)

…you will get all cavity 4 data and all cavity 2 data for last week ONLY.

Dynamic Date Filtering

Dynamic Date Filtering allows you to set non-specific dates, but dates that fall into a certain range. For example, you could look for any data filtered before This Week, where "This Week" would always be whatever week today's date was currently in.

When you choose a date field such as Measure Date, you will have comparison operators; “is before”, “is after”, “is in the last”, “is in the range”, and “is in the keyword range”. The “is before”, “is after”, and “is in the range” options allow you to specify actual dates such as “9/1/2008”.

The “is in the last” option allows you to choose a number and keyword to specify a time period leading up to the current time. For instance, “is in the last 6 weeks”, “is in the last 5 minutes” or “is in the last 2 months”. The date range is then calculated automatically as the filter is applied.

Finally, the “is in the keyword range” option allows you to specify a particular time period that may or may not lead up to the current time. The keyword chosen will automatically calculate to the correct dates when the filter is applied to the data. The list of keywords along with the definitions is below.

Options Tab

Filter Format

By default, when filters are used in reports the name of the record filter is displayed on the report. This is very useful until you have dates or dynamic values (i.e. via latest received or ask filters). For instance, if you had a filter that showed data where the measure date was last month and you named it “September 2008”, then your report would appear correctly if you ran it in October 2008 but would quickly be inaccurate in November since the label would still say “September 2008” even though the data was for October (last month).

The Filter Format option allows a dynamic name to be used on your reporting that is generated by keywords you create. These keywords can insert things like the value in the search conditions or the current day of the week, the current date, the current month, the current year, etc. This allows for easier naming and organization of your reports. If a dynamic filter format has been specified for a filter, it will be used in place of the actual filter name wherever the filter is used. This includes all charting (Analysis screen, dashboards, reports) and text (report tables, dashboard labels).

To dynamically format your filter name, click the pencil () in the Filter Format area of the Options tab in Advanced mode and the Filter Format Editor will appear. Choose a keyword from the list at the top and click Add to add it to the filter format text field below. In the example, we chose 2_MONTHS_AGO which added [2_MONTHS_AGO] to the text field.

At the bottom of the screen is a preview called Current Value. By default, the date will be displayed as the first day of two months ago. To change the format, you can enter a colon (:) character just inside the square brackets and then enter the formatting of your choice. In this case, we added MMMM which tells QC-CALC to use the full name of the month. Thus, [2_MONTHS_AGO] became [2_MONTHS_AGO:MMMM]. Make sure the formatting is placed inside the square brackets or it will not be recognized.

You can use any of the following date constants and combine them to form unlimited combinations. Please keep in mind that the constants are case sensitive. Therefore “m” and “M” are different. For sake of example, the date October 1, 2008, will be used.

These can be combined to form various combinations of dates as desired. Below are a few examples using October 1, 2008.

MMM yy – Oct 08

MMM/yyyy – Oct/2008

MM/dd/yyyy – 10/01/2008

MMMM yyyy – October 2008

Filter Format with Values

You can also display the values of your search conditions by using the [Values:1] keyword in the filter format. When using this keyword, the tag will be replaced by the specific search condition’s values based on the number specified. If the number refers to a search condition that uses a BETWEEN query, both values will be used.

As an example, let's say you have a filter that gets all the data for Joe made on Machines 1 through 3 for the past week.

Example Filter Conditions:

WHERE Operator = Joe

AND Machine BETWEEN 1 and 3

If you set the format to [Values:1], it will substitute "Joe" into the label at run time since that is the value from the first search condition. If you specify [Values:2], it will substitute "1 - 3" into the label at run time since it is the second search condition. Note that because there is a "BETWEEN" filter being applied, it automatically adds both values separated by a dash.

You can mix [values] keywords and regular words to make interesting labels. For instance:

"[Values:1]'s parts made on Machines [Values:2]"

will yield

"Joe's parts made on Machines 1 - 3"

in the example above.

Values keywords can also be used with either Ask or Latest value queries. They work the exact same way but substitute the received values. As another example, if you have a report where you want to pull the last 5 lots but don’t know the actual lot numbers, you can use the latest values filtering to create 5 filters (latest lot, 2nd latest lot, 3rd latest lot, and so on). Then you can specify “Lot [Values:1]” as the filter format for each of them and the correct lot number will be displayed on your report regardless of when you run the report.

Custom Trace field Lists

Custom trace field lists allow you to sort out confusion or difficulties that arise when similar trace field names are used for the same trace fields. For example, Part, Part #, and Part No may all refer to the part number, but all have different trace field names because they come from different inspection routines on your CMMs. With the Custom Trace field Lists option, you let ERS know which trace field names refer to the same thing. The name of the Custom Trace field lists then appears at the bottom of the Filter On list as a field.

Using our example above, we could create a Custom Trace field List called “My Part Number” that contains three spelling variations of part number: Part, Part #, and Part No. If you wanted to filter on all parts where the part number is between 5 and 7, then you would simply choose “My Part Number” from the Filter On list, choose “is between” and then enter 5 and 7 in the two criteria boxes. The query will automatically search for variations in the trace field name and filter correctly.

Note: Using the Edit Custom Trace field List () button in the Filters area of the Data View area is an easy way to add new name variations to your list at a later time. You can add a new variation at any time, and it will automatically be used the next time the Trace field List is used in Filtering.

Wildcards are not allowed in the variations of trace field names because it could cause unintended results. Therefore, you cannot enter “Part*” or “Part%” and assume any trace field name that starts with the word “Part” will be picked up.

SQL Tab – Viewing the Generated SQL

This tab allows you to view the SQL that is generated by the filter you've created. You can copy and paste the statement into your own Query Analyzer if you wish, but you cannot edit the SQL from this tab. To change the SQL, edit the filters you've set or the options on the Tables tab. The SQL refreshes itself when you view the tab and not when you make changes in the filter criteria above. Therefore, if you have the SQL tab displayed and would like to see your changes, simply click the Refresh link on the tab and it will update to reflect changes to your criteria at the top.

Latest Value Filtering Explained

Latest value filtering allows you to dynamically find the latest value of a particular Text or Numeric Trace field for use in a filter. This is particularly useful for ongoing jobs and dashboards because it automatically reflects changes in the data without having to maintain your filters. For instance, you may want to create a dashboard that shows data for the latest lot of parts. Since you don’t always know the exact lot number to use and since it constantly changes as more parts are measured, using a filter that retrieves the “latest lot” allows you to set it up and run it on a schedule without having to update it.

ERS includes up to 10 latest values dependent and 10 latest values independent for each Numeric and Text Trace field. Continuing with the “lot” example above, you now have a convenient method to report a running history of the last 5 lots. To do this, you would create a data group with 5 cycling filters. Each one filters on the latest lot, 2nd to last lot, 3rd to last lot, etc.

Please keep in mind that this filtering works even if the values are mixed, repeated, or out of order. For instance, let's say you have 10 parts with different lot numbers in the following order.

1, 2, 3, 2, 5, 6, 6, 7, 8, 7

Notice they are out of order and repeating in certain cases. Using the Latest Value filtering, the following are the values for the last few numbers:

The latest value is 7.

The 2nd latest value is 8.

The 3rd latest value is 6 (even though 7 repeats - it was already counted so it isn’t counted again).

The 4th latest value is 5 (even though 6 repeats).

The following two topics explain the difference between dependent and independent latest value filters. As an example, we’ll use the following sample dataset that contains 20 parts measured over two months with two trace fields: Lot and Operator.

Dependent vs. Independent Latest Value Filters

To put it simply, an independent latest value will get you the latest value in the part file regardless of any other filter criteria or the global filter (in the case of Analysis, reports, dashboards, etc.). A dependent latest value filter will get you the latest value based on the other filter criteria either in the same filter or in the global filter.

In the example above, if we wrote a filter and chose data where Lot = Latest Value (independent), then the filter would return data for Lot 2 since that is the value in line 20 and it is the latest Lot value for that part file. This is mostly the same for dependent latest value filters. The two diverge when you combine them with other filter criteria.

To explain, consider the following two queries:

WHERE Lot IS Latest Value (independent)

AND measure date IS BETWEEN 10/1/2013 AND 10/31/2013

This query first gets the independent latest lot (which is 2 or last record in the file). Then it combines this with data from October. This will result in one record being returned which IS record 9 in the dataset above.

vs.

WHERE Lot IS Latest Value (dependent)

AND measure date IS BETWEEN 10/1/2013 AND 10/31/2013

This query considers both criteria together. It first gets data for October and then looks for the latest lot in October (which is 1). It then returns data where the Lot is 1 in October which is record 10 in the dataset above.

Notice how the “independent latest” always gets the absolute latest value in the file and then gets data for the other criteria. The “dependent latest” value first filters by other criteria (date) and then finds the latest value based on the filtered results. This effectively makes the latest value filter “dependent” on the other filters hence the name.

To create another example, you could have the following query:

WHERE Lot IS 2nd Latest Value (dependent)

AND measure date IS BETWEEN 10/1/2013 AND 10/31/2013

AND Operator IS ‘Bruce’

This query will get Bruce’s results for October and will then look for the second latest value for Lot (which is 3). The query then becomes “Bruce’s data for October where the Lot = 3” which yields record 7.

If we did the same thing with an “independent 2nd latest value” for lot, we would end up with “Bruce’s data for October where Lot = 7”. This would return records 2 and 6.

WHERE Lot IS 2nd Latest Value (independent)

AND measure date IS BETWEEN 10/1/2013 AND 10/31/2013

AND Operator IS ‘Bruce’

Combining Latest Value Filters

When you combine two latest value filters, you can get more advanced results. First, it should be stated that it doesn’t make sense to combine two dependent latest value filters since it would create a circular reference. For instance, let’s say you had the following query:

WHERE Lot is Latest Value (dependent)

AND Operator is Latest Value (dependent)

This could be processed two ways. You could either get the latest Operator for the latest Lot or you could get the latest Lot for the latest Operator. Depending on your data, this could give you two very different results. For this reason, it is not recommended that you mix dependent latest value queries together. If you do, QC-CALC will automatically flip the first one to an independent latest value query. Then it will process the second filter as a dependent latest value based on the independent latest value.

Other than that scenario, you are free to combine independent latest value filters with dependent latest value filters. Typically, you will be combining something concrete such as Last Month or other typical filter criteria.

Here are some combined examples:

WHERE Lot IS Latest Value (dependent)

AND Operator IS 2nd Latest Value (independent)

This query will first get the absolute 2nd latest operator (which is Ben). Then it will get the latest Lot for Ben (which is 7). Therefore, this query becomes “Ben’s data for Lot 7” which returns record 19.

If we add a date filter on to that:

WHERE Lot IS Latest Value (dependent)

AND Operator IS 2nd Latest Value (independent)

AND measure date IS BETWEEN 10/1/2013 AND 10/31/2013

Once again, this query gets the independent latest operator (Ben). Then it filters data for October and finds the latest Lot for Ben (which is 2). Therefore, this query becomes “Ben’s data for Lot 2 in October 2013” which returns record 9.