Stratifying data

Stratifying groups the records in a table into numeric intervals (value ranges) based on values in a numeric field, and counts the number of records in each interval.

For example, you could stratify an accounts receivable table on the invoice amount field to group records into $5000 intervals – invoices from $0 to $4,999.99, from $5,000 to $9,999.99, and so on – and to find the total number of transactions, and the total transaction amount, for each interval.

Subtotaling associated numeric fields

When stratifying, you can optionally subtotal one or more associated numeric fields. In the example above, you could subtotal the discount amount field to find the total discount amount for each interval.

Note

If you do not specify a subtotal field, the field you are stratifying on is automatically subtotaled.

How numeric intervals work

Numeric intervals are value ranges. You have two options when creating numeric intervals:

  • equal-sized intervals
  • custom-sized intervals

Equal-sized intervals

Analytics calculates equal-sized intervals by grouping the values in the key field into a specified number of intervals.

To create equal-sized intervals, you specify the minimum value of the first interval and the maximum value of the last interval, and the number of intervals you want.

Tip

If you use the actual minimum and maximum values in the field, the interval size is typically not a round amount. If you want the interval size to be a round amount, you can specify minimum and maximum values in round amounts – for example, 0 and 5000.

Custom-sized intervals

Analytics calculates custom-sized intervals by grouping the values in the key field into intervals with starting values that you specify.

To create custom-sized intervals, you specify the start value of each interval and the end value of the last interval. You can create equal-sized intervals, or intervals that vary in size.

Examples of equal-sized and custom-sized intervals

The table below shows examples of the types of intervals that you could create for a set of values that ranges from $48.19 to $4,792.83.

Equal-sized intervals

(using actual min and max values)

 Equal-sized intervals

(min and max specified as round numbers)

 Custom-sized intervals
48.19 - 997.11

997.12 - 1,946.04

1,946.05 - 2,894.97

2,894.98 - 3,843.90

3,843.91 - 4,792.83

 0.00 - 999.99

1,000.00 - 1,999.99

2,000.00 - 2,999.99

3,000.00 - 3,999.99

4,000.00 - 5,000.00

 0.00 - 99.99

100.00 - 999.99

1,000.00 - 5,000.00

Stratifying and sorting

You can stratify sorted or unsorted tables. When you stratify an unsorted table Analytics automatically sorts the output results as part of the stratify operation.

The Statistics option

The Include Statistics for Subtotal Fields option allows you to calculate average, minimum, and maximum values for each subtotaled numeric field. In the example above, using the statistics option would calculate the average, minimum, and maximum invoice amounts in each interval, and the average, minimum, and maximum discount amounts in each interval if you also subtotaled the discount amount field.

Stratifying in detail

Stratifying performs the following operations:

Operation

Location in Stratify results

Groups the records into intervals based on a numeric field

Trans Amount field, first

Counts (subtotals) the number of records falling into each interval, and calculates the percentage of the total count represented by each subtotal

Count field

Percent of Count field

Provides the minimum and maximum values in the numeric field being stratified

not shown

Optionally subtotals the values of one or more numeric fields for each interval, and for the first selected field calculates the percentage of the field total represented by each subtotal

Trans Amount field, second

Percent of Field field

Optionally calculates average, minimum, and maximum values for each subtotaled numeric field

not shown

Provides totals for all numeric fields included in the output results

Totals row

Optionally breaks down the output results based on the values in a character field such as customer ID or transaction type (requires that the character field is sorted prior to stratifying)

not shown

Stratify results

Output results produced by:

  • stratifying on transaction amount in an accounts receivable table

    (the Ar table in ACL DATA\Sample Data Files\Sample Project.ACL)

  • using $1000 intervals
  • outputting the results to screen

Steps

You can stratify data by grouping the records in a table into equal-sized, or custom-sized, numeric intervals.

For each interval, you can optionally include the following calculations for associated numeric fields: subtotal, average value, minimum value, maximum value.

  1. Select Analyze > Stratify.
  2. On the Main tab, do one of the following:

    Select a field to stratify on from the Stratify On drop-down list.

    Click Stratify On to select the field, or to create an expression.

  3. Optional. Select one or more Subtotal Fields, or click Subtotal Fields, to select the subtotal field(s), or to create an expression.

    If you do not select a subtotal field, the field you are stratifying on is automatically subtotaled. You must explicitly select the stratify field if you want to subtotal it along with one or more other fields, or if you want to include statistics for the subtotaled stratify field.

    The order in which you select the subtotal fields is the order in which the columns appear in the results. If you are appending results to an existing Analytics table, the column selection and order must be identical to the column selection and order in the existing table.

  4. In Minimum, enter the minimum value of the first interval.

    If you previously performed a profile or statistics operation on the stratify field, the lowest value in the stratify field is automatically entered by default. You can change the default, if required.

  5. In Maximum, enter the maximum value of the last interval.

    If you previously performed a profile or statistics operation on the stratify field, the highest value in the stratify field is automatically entered by default. You can change the default, if required.

  6. Do one of the following:

    Select Intervals, and enter the number of equal-sized intervals that you want in the range specified by the Minimum and Maximum values. The default number of intervals is 10.

    Tip

    You can change the default number of intervals by selecting Tools > Options and updating the Intervals number on the Command tab.

    Select Free to create custom-sized intervals, and enter the start value of each interval and the end value of the last interval. You must enter each value on a separate line.

    Specifying Minimum and Maximum values is optional when you use Free. If you do specify Minimum and Maximum values, those values are the start point of the first interval and the end point of the last interval, and the values you enter create additional intervals within the range. The values you enter must be greater than the value specified in Minimum, and equal to or less than the value specified in Maximum.

  7. 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.

  8. Optional. Select Include Statistics for Subtotal Fields if you want to calculate average, minimum, and maximum values for each subtotaled numeric field.

    You must select at least one subtotal field in order to include statistics.

  9. Click the Output tab.
  10. Select the appropriate output option in the To panel:
    • Screen – Select this option to display the results in the Analytics display area. If the output table contains a large number of records, it is faster and more useful to save the results to a file than to display the results on the screen.
    • Print – Select this option to send the results to the default printer.
    • Graph – Select this option to create a graph of the results and display it in the Analytics display area.
    • File – Select this option to save or append the results to an Analytics table or a text file. If you save or append to an Analytics table, the table is added to the open project if it is not already in the project. If you save or append to a text file, the file is saved outside Analytics.

    Note

    Output options that do not apply to a particular analytical operation are disabled.

  11. If you selected File as the output type, specify the following information in the As panel:
    • File Type – Select Analytics Table to save the results to a new Analytics table, or append the results to an existing Analytics table. Select ASCII Text File or Unicode Text file (depending on which edition of Analytics you are using) to save or append the results to a text file.
    • Name – Enter a file name in the Name text box. Or click Name and enter the file name, or select an existing file in the Save or Save File As dialog box to overwrite or append to the file. If Analytics prefills a file name, you can accept the prefilled name, or change it.

      You can also specify an absolute or relative file path, or navigate to a different folder, to save or append the file in a location other than the project location. For example: C:\Results\Output.fil or Results\Output.fil.

    • Local – Only enabled when connected to a server table and saving or appending the results to an Analytics table. Select Local to save the file to the same location as the project, or to specify a path or navigate to a different local folder. Leave Local deselected to save the file to the Prefix folder on a server.

    Note

    For output results produced from analysis or processing of AX Server tables, select Local. You cannot deselect the Local setting to import results tables to AX Server.

  12. Depending on the output type, you can optionally specify a Header and/or a Footer in the text box(es).

    Headers and footers are centered by default. Type a left angle bracket (<) before the header or footer text to left align the text. Click Header or Footer to enter a header or footer of more than one line. Alternatively, you can enter a semi-colon (;) as a line-break character in the header or footer text box. Left aligning multiple lines requires a left angle bracket at the beginning of each line.

  13. Click the More tab.
  14. Select the appropriate option in the Scope panel:
    • All – This option is selected by default. Leave it selected to specify that all records in the view are processed.
    • First – Select this option and enter a number in the text box to start processing at the first record in the view 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 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 view based on a particular criterion or set of 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 in the view to be processed only while the specified condition evaluates to true. As soon as the condition evaluates to false, the processing terminates, and no further records are considered. You can use the While option in conjunction with the All, First, or Next options. Record processing stops as soon as one limit is reached.

      • 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.

  15. If you do not want to include values that exceed the specified Minimum and Maximum values, select Suppress Others.
  16. If you want to break down the output results based on the values in a character field, enter the field name in the Break text box, or click Break to select the field, or to create an expression.

    For example, the results of stratifying an accounts receivable table by transaction amount could be further broken down by customer. Break can only be used with a single character field, so nested breakdowns are not supported.

    Note

    For the Break option to yield meaningful results, the character field used for the breakdown must be sorted prior to stratifying.

  17. If you selected File as the output type, and want to append the output results to the end of an existing file, do one of the following:

    Select Append To Existing File if you are appending to a text file, or to an Analytics table that you are certain is identical in structure to the output results.

    Leave Append To Existing File deselected if you are appending to an Analytics table and you want Analytics to compare the record lengths of the output results and the existing table. If the record lengths are not identical, the data structure is not identical, and the append will not work correctly.

    Note

    Leaving Append To Existing File deselected is recommended if you are uncertain whether the output results and the existing table have an identical data structure. For more information about appending and data structure, see Appending results to Analytics tables and text files.

  18. If you selected File (Analytics Table) as the output type, select or deselect Use Output Table depending on whether or not you want the Analytics table containing the output results to open automatically upon completion of the operation.
  19. Click OK.
  20. If the overwrite prompt appears, select the appropriate option.

    If you are expecting the Append option to appear and it does not, click No to cancel the operation and see Appending results to Analytics tables and text files.

[ Back to top ]

Analytics 14.1 Help