AGE command

Used to group 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>> <IF test> <WHILE test> <{FIRST|NEXT} range>
<TO SCREEN|filename|GRAPH|PRINT> <KEY break_field> <HEADER header_text>
<FOOTER footer_text> <APPEND> <LOCAL> <STATISTICS>

Parameters

ON date_field

Specifies the name of the date or datetime field or the expression to be aged. The ON parameter can be omitted.

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. Specifies the date that dates in date_field are compared to. You must specify the cutoff date as an unquoted string in YYMMDD or YYYYMMDD format, regardless of the format of the date field. For example: CUTOFF 20141231

The default cutoff date is the current system date.

INTERVAL days <,...n>

Optional. Specifies the date intervals (that is, number of days) to use in calculating the aging periods. The days values represent the beginning of each aging period measured backward in time from the 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.

For example: 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. Specifies that dates that fall outside the specified aging periods are excluded from the command output.

SUBTOTAL numeric_field <...n>

Optional. Specifies one or more numeric fields or expressions to subtotal for each aging period. Multiple fields must be separated by spaces. Specify ALL to subtotal all the numeric fields in the table.

IF test

Optional. Specifies a condition that must be met. The command is executed on only those records that meet the condition.

WHILE test

Optional. Specifies that the command is executed only while a condition is true. The command processing terminates as soon as the specified condition evaluates as false, or the end of the table is reached.

FIRST | NEXT range

Optional. Specify FIRST to start processing at the first record or NEXT to start processing at the currently selected record. Use the range value to specify the number of records to process.

TO SCREEN | filename | GRAPH | PRINT
  • Specify TO SCREEN to display the results in the ACL display area. This is the default output if the TO parameter is not specified.

  • Specify TO filename to save the results to a file. You must specify the filename value as a quoted string with the appropriate file extension. For example, specify the .TXT file extension to create a text file.

    For example: TO "Output.TXT"

    You can also specify an absolute or relative file path to save the file to an existing folder.

    For example: TO "C:\Output.TXT" or TO "Results\Output.TXT"

  • Specify TO PRINT to send the results to the default printer.

  • Specify TO GRAPH to display the results in a graph in the ACL display area.

KEY break_field

Optional. Specifies that a subtotal is calculated each time the value of the specified field or expression changes. You can specify only one field, but you can create and specify an expression that contains more than one field. The break_field must be a character field or expression.

HEADER header_text

Optional. Specifies the text to insert at the top of each page of a report. The header_text value must be specified as a quoted string. The value overrides any value set for the ACL HEADER system variable.

FOOTER footer_text

Optional. Specifies the text to insert at the bottom of each page of a report. The footer_text value must be specified as a quoted string. The value overrides any value set for the ACL FOOTER system variable.

APPEND

Optional. Specifies that command output is appended to the end of an existing file instead of overwriting it. When using this parameter, you must ensure that the structure of the command output and the existing file is identical, if maintaining a consistent file structure is important (for example, when you are appending data to an existing ACL table), because ACL appends output to an existing file regardless of its structure.

LOCAL

Optional. Specifies that the output file is saved in the same location as the ACL project. This parameter only applies when the command is run on an ACL Server table and the output file is an ACL table.

STATISTICS

Optional. Specifies that average, minimum, and maximum values are calculated for all SUBTOTAL fields. If you specify the STATISTICS parameter you must also specify the SUBTOTAL parameter with at least one field.

Remarks

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.

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.

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

You can create aging periods more recent than the cutoff date by entering negative values for date intervals. For example, INTERVAL -60,-30,0,30,60,90 would create aging periods running forward and backward from the cutoff date, an approach that could be used to create a date profile of all the records in a table using different points in time.

Example

The following example ages an accounts receivable table on the Invoice Date field, and subtotals the Invoice Amount field. The output results are grouped 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 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


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