Aging groups the records in a table into aging periods based on values in a date or datetime field, and counts the number of records in each period. You can also subtotal one or more numeric fields for each period.

Aging periods are based on date intervals (that is, number of days) measured backward in time from the current system date, or from a cutoff date you specify such as a fiscal period end date. Common uses of aging include evaluating sales trends, looking at transaction volumes, and grouping invoices by the number of days outstanding.

For example, you could age an accounts receivable table on the invoice date field to group records into 30-day periods – invoices from the cutoff date to 29 days previous, from 30 days previous to 59 days previous, and so on – and to find the total number of outstanding invoices for each period. You could also subtotal the invoice amount field to find the total outstanding invoice amount for each period.

You can specify date intervals such as 0, 90, and 120 days as starting points for aging periods, or you can accept the default settings of 0, 30, 60, 90, 120, and 10,000 days. An interval of 10,000 days, or an appropriate final interval you specify, is used to isolate records with dates that are probably invalid.

You can age on datetime values, however only the date portion of the values is considered. The time portion is ignored. You cannot age on time data alone.

## 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 for each aging period.

## Aging in detail

Aging performs the following operations:

Table 1. Operations performed by aging

Operation

Location in Figure 1

Groups the records into aging periods based on cutoff date and date intervals

Days field

Subtotals the number of records in each aging period, and calculates the percentage of the total count represented by each subtotal

Count field

Percent of Count field

Provides the minimum and maximum ages of records (that is, the most recent and the oldest)

not shown

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

Trans Amount field

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 aging)

not shown

If you output the results to screen or graph, the graph displays the count subtotals for each aging period, or the numeric subtotals if you include one or more numeric fields in the aging operation.

Figure 1 provides an example of the results produced by aging on invoice date, and subtotaling transaction amount, in an accounts receivable table (the ‘Ar’ table in ACL DATA\Sample Data Files\Sample Project.ACL), using 30-day aging periods, and outputting the results to screen.

Figure 1. Aging results