About stratifying

Stratifying groups the records in a table into equal-sized, or custom-sized, numeric intervals based on values in a numeric field, and counts the number of records in each interval. You can also subtotal one or more numeric fields for each interval. If you do not specify a subtotal field, the field you are stratifying on is automatically subtotaled.

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. You could also subtotal the discount amount field to find the total discount amount for each interval.

You can specify the minimum value of the first interval and the maximum value of the last interval and which fields to subtotal. Counts and totals for each interval are displayed as numbers and as percentages. Stratifying works on sorted and unsorted tables.

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 $5000 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:

Table 1. Operations performed by stratifying


Location in Figure 1

Groups the records into intervals based on a numeric field

Trans Amount field, first

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

Figure 1 provides an example of the 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, and outputting the results to screen.

Figure 1. Stratify results

Related tasks
Stratifying data

(C) 2015 ACL Services Ltd. All Rights Reserved.