Identifying outliers

Use the outlier feature in Analytics to identify records that are out of the ordinary and could require closer scrutiny.

What are outliers?

Outliers are records with numeric amounts that differ significantly from the numeric amounts in the records that they are grouped with.

Grouping records is optional

When examining data for outliers, you do not have to group the records. You may be interested in finding outliers across an entire table, rather than within specific groups.

How are outliers identified?

For each group of records, or for an entire set of records, Analytics uses the standard deviation of a specified numeric field, or a multiple of the standard deviation, to establish upper and lower outlier boundaries.

Any record with a value in the numeric field that is greater than the upper boundary, or less than the lower boundary, is an outlier and included in the output results.

Standard deviation is a measure of the dispersion of a data set – that is, how spread out the values are. The outliers calculation uses population standard deviation.

Positioning the outlier boundaries

You can position the outlier boundaries wherever you feel is appropriate, or you can test different positions and compare results.

To position the boundaries, you specify any positive multiple of the standard deviation of the outlier field: 0.5, 1, 1.5, and so on. For example, if you specify a multiple of 1.5, the outlier boundaries are 1.5 standard deviations above and below the mean or median of the values in the outlier field.

For the same set of data, as you increase the standard deviation multiple, you potentially decrease the number of outliers in the output results.

The distribution of data

The values in a set of numeric data are typically distributed over a range from smallest to largest. In a normal distribution, the values are evenly distributed around the center point of the data, forming a bell-shaped curve. The center point is often defined as the average or the mean of the values, but it could also be the median or the mode.

Guidelines

When you specify settings in the outliers feature, consider the nature of the data you are analyzing:

Nature of the data	Setting guideline
Values are clustered, with a small range	Use a smaller standard deviation multiple. Try starting with 1. Use decimal multiples such as 1.25, to make precise adjustments.
Values are dispersed, with a large range	Use a larger standard deviation multiple. Try starting with 3.
The data is skewed, with a small percentage of the values being large, or small, when compared to the rest of the data	Use Median, instead of Average, as the method for calculating the center point of the values that you are examining.

Adjusting based on the output results

• Too many results increase the standard deviation multiple
• Too few results, or no results reduce the standard deviation multiple

Keep in mind that you can use decimal multiples, and multiples less than 1. For example: 0.75.

Steps

1. Open the table that you want to test for outliers.
2. From the Analytics main menu, select Analyze > Outliers.
3. Under Method, select the method for calculating the center point of the values in the numeric field that you are examining:
   • Average
   • Median
4. In Number of times of S.dev, specify a multiple of the standard deviation to use for the outlier boundaries.

   You can specify any positive integer or decimal numeral (0.5, 1, 1.5, 2 . . . ).

5. Do one of the following:
   • From the Primary Keys list, select one or more key fields to use for grouping the records in the table.

     Tip

     You can Ctrl+click to select multiple non-adjacent fields, and Shift+click to select multiple adjacent fields.

   • Select No Key to identify outliers across the entire table, rather than within specific groups.
6. From the On Field list, select the numeric field to examine for outliers ("the outlier field").
7. Optional. From the Other Fields list, select one or more additional fields to include in the output table.

   Note

   Key fields and the outlier field are automatically included in the output table, and do not need to be selected.

8. If there are records in the current view that you want to exclude from processing, enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.

   Note

   The If condition is evaluated against only the records remaining in a table after any scope options have been applied (First, Next, While).

   The IF statement considers all records in the view and filters out those that do not meet the specified condition.

9. Do one of the following:
   1. In the To text box, specify the name of the output table.
   2. Select Screen to output the results to the Analytics display area.
10. Deselect Presort, if appropriate.

    Note

    Guidance is provided below.

11. On the More tab:
    1. Optional. To specify that only a subset of records are processed, select one of the options in the Scope panel.
    2. Optional. Select Use Output Table if you want the output table to open automatically.
    3. Click OK.

Outliers dialog box options

The tables below provide detailed information about the options in the Outliers dialog box.

Main tab

Options – Outliers dialog box	Description
Average Median	The method used for calculating the center point of the values in the outlier field. Average use the average (mean) of the values in the field Median use the median of the values in the field The center point is used in calculating the standard deviation of the values in the outlier field. Note If you select Median, the outlier field must be sorted. Select Presort if the outlier field is not already sorted. Tip If the data you are examining for outliers is significantly skewed, Median might produce results that are more representative of the bulk of the data.
Number of times of S.dev	In the outlier field, the number of standard deviations from the mean or the median to the upper and lower outlier boundaries. You can specify any positive integer or decimal numeral (0.5, 1, 1.5, 2 . . . ) For example, specifying 2 establishes, for each key field group, or for the field as a whole: an upper outlier boundary 2 standard deviations greater than the mean or the median a lower outlier boundary 2 standard deviations less than the mean or the median Any value in the outlier field greater than an upper boundary, or less than a lower boundary, is included as an outlier in the output results. Note For the same set of data, as you increase the number of standard deviations, you potentially decrease the number of outliers in the output results.
Primary Keys optional	The field or fields to use for grouping the data in the table. For each key field group, a standard deviation is calculated for the group's numeric values in the outlier field. The group standard deviation is used as the basis for identifying group outliers. Key fields can be character, numeric, or datetime. Multiple fields can be any combination of data types. If you select more than one field, you created nested groups. The nesting follows the order in which you select the fields. Note The key field or fields must be sorted. Use Presort if one or more fields are not already sorted.
No Key optional	Do not group the data in the table. A standard deviation is calculated for the outlier field as a whole. The field standard deviation is used as the basis for identifying field outliers.
On Field ("the outlier field")	The numeric field to examine for outliers. You can examine only one field at a time. If you select a key field, outliers are identified at the group level. If you select No Key, outliers are identified at the field level.
Other Fields optional	One or more additional fields to include in the output. Note Key fields and the outlier field are automatically included in the output table, and do not need to be selected.
If optional	Allows you to create a condition to exclude records from processing. You can enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.
To optional	Specifies the name and location of the output table. To save the output table to the Analytics project folder enter only the table name. To save the output table in a location other than the project folder specify an absolute or relative file path, or click To and navigate to a different folder. For example: C:\Results\Output.fil or Results\Output.fil. Regardless of where you save the output table, it is added to the open project if it is not already in the project. If Analytics prefills a table name, you can accept the prefilled name, or change it.
Screen optional	Displays the results in the Analytics display area instead of creating an output table.
Presort optional	Performs a sorting operation before executing the command. If you select Presort and: Sorts by: One or more key fields Average key field or fields key field or fields, then by the outlier field (if the outlier field is computed) Note Sorting a computed outlier field is an internal, technical requirement of Analytics. One or more key fields Median key field or fields, then by the outlier field No Key Average no sorting No Key Median the outlier field Tip If the appropriate field or fields in the input table are already sorted, you can save processing time by not selecting Presort.

More tab

Options – Outliers dialog box	Description
Scope panel	Specifies which records are processed: All (default) all records in the table are processed. First select this option and enter a number in the text box to start processing at the first record in the table and include only the specified number of records. Next select this option and enter a number in the text box to start processing at the currently selected record in the table view and include only the specified number of records. The actual record number in the leftmost column must be selected, not data in the row. While select this option to use a WHILE statement to limit the processing of records in the table based on criteria. You can enter a condition in the While text box, or click While to create a WHILE statement using the Expression Builder. A WHILE statement allows records to be processed only while the specified condition evaluates to true. You can use the While option in conjunction with the All, First, or Next options. Note The number of records specified in the First or Next options references either the physical or the indexed order of records in a table, and disregards any filtering or quick sorting applied to the view. However, results of analytical operations respect any filtering. If a view is quick sorted, Next behaves like First.
Use Output Table	Specifies whether the Analytics table containing the output results opens automatically upon completion of the operation.
OK	Executes the operation. If the overwrite prompt appears, select the appropriate option.