AGE command

Used to create aged summary reports.

Syntax

AGE <ON> date_field_name <CUTOFF cutoff_date> <INTERVALS interval <,..n>>
<SUBTOTAL numeric_field <...n>> <IF test> <WHILE test> <{FIRST|NEXT} range>
<TO SCREEN|filename|GRAPH|PRINT> <KEY break_field> <SUPPRESS>
<HEADER header_text> <FOOTER footer_text> <APPEND> <LOCAL> 

Parameters

ON date_field_name

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_name 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 20111231

The default cutoff date is the current system date.

INTERVALS interval

Optional. Specifies the intervals to use in the calculation. The first entry identifies the beginning of the aging period. The last entry represents the end of the final period. You must specify the intervals as an unquoted string with comma separated values.

For example: INTERVALS 0,90,180,270,365

The default aging periods are 0, 30, 60, 90, 120, and 10,000 days. The 10,000 days period is used to isolate items with invalid dates. If required, these ranges can be customized to mirror other internal aging reports. The number of records for each aging period are displayed in the Count column, along with numeric field totals for those records in each Age period.

SUBTOTAL numeric_field_name

Optional. Specifies the numeric fields to be subtotaled.

IF test

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

WHILE test

Optional. Specifies that the command should only be executed while a condition is true. The command processing terminates as soon as the specified test 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 on screen in ACL. This is the default output if the TO parameter is not specified.

  • Specify TO filename to write 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 an existing folder to write the file to.

    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 create a graph of the command results and display it on screen in ACL.

KEY break_field

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

SUPPRESS

Optional. Specifies that values above the MAXIMUM value and below the MINIMUM value should be excluded from the command output.

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 should be appended to the end of an existing file instead of overwriting the existing file. You must ensure that the structure of the two files is identical when using this parameter, if maintaining a consistent file structure is important (e.g., 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 should be 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.

Remarks

The output for this command has columns for the aging intervals, the number of records in each interval, and the percent of the total records that each interval represents. If one or more fields were subtotaled, additional columns are displayed: another percent column, and the field subtotals for each interval.

Each percent column points to the column it represents. The right-hand percent column points to the nearest subtotaled field column, and shows the percent of the total value that the value of this interval represents. Percentages are not provided for additional subtotaled field columns.

Dates prior to the cutoff date fall into one of the specified aging periods. ACL creates another period, < 0, for dates more recent than the cutoff date.

Example

The following example modifies the default aging intervals and outputs the results to a text file.

AGE ON HireDate CUTOFF 20110225 INTERVAL 0,90,180,10000 SUPPRESS
SUBTOTAL Salary IF Salary > 1000 TO "AgeOutput.TXT" KEY CardNum 


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback