AGE command

Concept Information

Groups records into aging periods based on values in a date or datetime field. Counts the number of records in each period, and also subtotals specified numeric fields for each period.

Syntax

AGE <ON> date_field <CUTOFF cutoff_date> <INTERVAL days <,...n>> <SUPPRESS> <SUBTOTAL numeric_field <...n>|SUBTOTAL ALL <EXCLUDE numeric_field <...n>>> <IF test> <WHILE test> <FIRST range|NEXT range> <TO {SCREEN|filename|GRAPH|PRINT}> <KEY break_field> <HEADER header_text> <FOOTER footer_text> <APPEND> <STATISTICS>

Parameters

Name	Description
ON date_field	The name of the date or datetime field or the expression to be aged. Although you can age on a datetime field, only the date portion of datetime values is considered. The time portion is ignored. You cannot age on time data alone.
CUTOFF cutoff_date optional	The date that values in date_field are compared against. You must specify cutoff_date as an unquoted string in YYMMDD or YYYYMMDD format, regardless of the format of the date field. For example: CUTOFF 20141231 If you omit CUTOFF, the current system date is used as the cutoff date.
INTERVAL days <,...n> optional	The date intervals (that is, number of days) to use in calculating the aging periods. days represents the beginning of each aging period measured backward in time from cutoff_date: the first days value identifies the beginning of the first aging period a first days value of '0' specifies that the first aging period begins on the specified cutoff_date the last days value identifies the end of the final aging period You must specify the intervals as an unquoted string with comma separated values: INTERVAL 0,90,180,270,365 The default aging periods are 0, 30, 60, 90, 120, and 10,000 days. An interval of 10,000 days is used to isolate records with dates that are probably invalid. If required, date intervals can be customized to mirror other internal aging reports.
SUPPRESS optional	Suppresses dates that fall outside the aging period from the command output.
SUBTOTAL numeric_field <...n> \| SUBTOTAL ALL optional	One or more numeric fields or expressions to subtotal for each group. Multiple fields must be separated by spaces. Specify ALL to subtotal all the numeric fields in the table.
EXCLUDE numeric_field optional	Only valid when using SUBTOTAL ALL. The field or fields to exclude from the command. EXCLUDE allows you to fine-tune SUBTOTAL ALL, by excluding the specified fields. EXCLUDE must immediately follow SUBTOTAL ALL. For example: SUBTOTAL ALL EXCLUDE field_1 field_2
IF test optional	A conditional expression that must be true in order to process each record. The command is executed on only those records that satisfy the condition. Note The IF parameter is evaluated against only the records remaining in a table after any scope parameters have been applied (WHILE, FIRST, NEXT).
WHILE test optional	A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached. Note If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached.
FIRST range \| NEXT range optional	The number of records to process: FIRST start processing from the first record until the specified number of records is reached NEXT start processing from the currently selected record until the specified number of records is reached Use range to specify the number of records to process. If you omit FIRST and NEXT, all records are processed by default.
TO SCREEN \| filename \| GRAPH \| PRINT	The location to send the results of the command to: SCREEN displays the results in the Analytics display area Tip You can click any linked result value in the display area to drill down to the associated record or records in the source table. filename saves the results to a file Specify filename as a quoted string with the appropriate file extension. For example: TO "Output.TXT" By default, the file is saved to the folder containing the Analytics project. Use either an absolute or relative file path to save the file to a different, existing folder: TO "C:\Output.TXT" TO "Results\Output.TXT" GRAPH displays the results in a graph in the Analytics display area PRINT sends the results to the default printer
KEY break_field optional	The field or expression that groups subtotal calculations. A subtotal is calculated each time the value of break_field changes. break_field must be a character field or expression. You can specify only one field, but you can use an expression that contains more than one field.
HEADER header_text optional	The text to insert at the top of each page of a report. header_text must be specified as a quoted string. The value overrides the Analytics HEADER system variable.
FOOTER footer_text optional	The text to insert at the bottom of each page of a report. footer_text must be specified as a quoted string. The value overrides the Analytics FOOTER system variable.
APPEND optional	Appends the command output to the end of an existing file instead of overwriting it. Note You must ensure that the structure of the command output and the existing file are identical: the same fields the same field order matching fields are the same length matching fields are the same data type Analytics appends output to an existing file regardless of its structure. If the structure of the output and the existing file do not match, jumbled, missing, or inaccurate data can result.
STATISTICS optional	Note Cannot be used unless SUBTOTAL is also specified. Calculates average, minimum, and maximum values for all SUBTOTAL fields.

Examples

Age invoices with subtotaled amounts

You want to age an accounts receivable table on the Invoice_Date field and subtotal the Invoice_Amount field.

Invoices are grouped into 30-day periods:

from the cutoff date to 29 days previous
from 30 days previous to 59 days previous
so on

The results include the total outstanding invoice amount for each period:

OPEN Ar
AGE ON Invoice_Date CUTOFF 20141231 INTERVAL 0,30,60,90,120,10000 SUBTOTAL Invoice_Amount TO SCREEN

Remarks

For more information about how this command works, see Aging data.

Aging periods

The AGE command groups records into aging periods based on values in a date or datetime field. The output results contain a single record for each period, with a count of the number of records in the source table that fall into each period.

Interval measurement

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.

Future periods

You can create aging periods more recent than the cutoff date by entering negative values for date intervals. For example, the following creates aging periods running forward and backward from the cutoff date:

INTERVAL -60,-30,0,30,60,90

This approach creates a date profile of all the records in a table using different points in time.

Common use cases

Common uses of aging include evaluating sales trends, looking at transaction volumes, and grouping invoices by the number of days outstanding.

Analytics automatically creates one or two additional aging periods for any dates that fall outside the specified aging periods, assuming you are not using SUPPRESS.