Referring to Columns in Calculations

You can perform a calculation for one column or for a specific set of columns. To identify the columns, place the column number in parentheses after the label name.

Example: Referring to Column Numbers in a RECAP Expression

DEFINE FILE LEDGER
CUR_YR/I5C=AMOUNT;
LAST_YR/I5C=.87*CUR_YR - 142;
END
 
TABLE FILE LEDGER
SUM CUR_YR AS 'CURRENT,YEAR'
LAST_YR AS 'LAST,YEAR'
FOR ACCOUNT
1010 AS 'CASH ON HAND'                             OVER
1020 AS 'DEMAND DEPOSITS'                          OVER
1030 AS 'TIME DEPOSITS'                            OVER
BAR                                                OVER
RECAP TOTCASH/I5C = R1 + R2 + R3; AS 'TOTAL CASH'  OVER
" "                                                OVER 
RECAP GROCASH(2)/F5.2 = 100*TOTCASH(1)/TOTCASH(2) - 100; 
AS 'CASH GROWTH(%)'
END

In the second RECAP expression, note that:

• TOTCASH(1) refers to total cash in column 1.
• TOTCASH(2) refers to total cash in column 2.
• The resulting calculation is displayed in column 2 of the row labeled CASH GROWTH(%).

The RECAP value is only calculated for the column specified.

The output is shown in the following image.

After data retrieval is completed, a single column is calculated all at once, and multiple columns one by one.

When a set of contiguous columns is needed within a RECAP, you can separate the first and last column numbers with commas. For example, DIFFERENCE (2,5) indicates that you want to compute the results for columns 2 through 5.

Example: Recapping Over Contiguous Columns

In this example, the RECAP calculation for ATOT occurs only for columns 2 and 3, as specified in the request. No calculation is performed for column 1.

DEFINE FILE LEDGER
CUR_YR/I5C=AMOUNT;
LAST_YR/I5C=.87*CUR_YR - 142;
NEXT_YR/I5C=1.13*CUR_YR + 222;
END
 
TABLE FILE LEDGER
SUM NEXT_YR CUR_YR LAST_YR
FOR ACCOUNT
10$$ AS 'CASH'                      OVER
1100 AS 'ACCOUNTS RECEIVABLE'       OVER
1200 AS 'INVENTORY'                 OVER
BAR                                 OVER 
RECAP ATOT(2,3)/I5C = R1 + R2 + R3; 
AS 'ASSETS--ACTUAL'
END

The output is shown in the following image.

When you need a calculation for every other or every third column instead of every column, you can supply a factor, or column address, to do this. Column addressing is useful when several data fields are displayed within each value of a column sort.

A calculation can use a specific column as a base, and refer to all other columns by their displacement from that column. The column to the left of the base column has a displacement of -1 relative to the base column. The column to the right has a displacement of +1. For example,

COMP=FIX(*)-FIX(*-1);

can refer to the change in fixed assets from one period to the next. The reference to COMP=FIX(*) is equivalent to COMP=FIX.

When referring to a prior column, the column must already have been retrieved, or its value is zero.

This example computes the change in cash (CHGCASH) for columns 1 and 2.

DEFINE FILE LEDGER
CUR_YR/I5C=AMOUNT;
LAST_YR/I5C=.87*CUR_YR - 142;
NEXT_YR/I5C=1.13*CUR_YR + 222;
END
 
TABLE FILE LEDGER
SUM NEXT_YR CUR_YR LAST_YR
FOR ACCOUNT
10$$ AS 'TOTAL CASH' LABEL TOTCASH           OVER
" "                                          OVER 
RECAP CHGCASH(1,2)/I5SC = TOTCASH(*) - TOTCASH(*+1); AS 'CHANGE IN CASH' 
END

The output is shown in the following image.

Column notation assigns a sequential column number to each column in the internal matrix created for a report request. If you want to control the creation of column reference numbers for the columns that are used in your report, use the CNOTATION column notation command.

Because column numbers refer to columns in the internal matrix, they are assigned after retrieval and aggregation of data are completed. Columns created and displayed in a report are stored in the internal matrix, and columns that are not displayed in a report may also be generated and stored in the internal matrix. Columns stored in the internal matrix include calculated values, reformatted field values, BY fields, fields with the NOPRINT option, and certain RECAP calculations such as FORECAST and REGRESS. Every other column in the internal matrix is assigned a column number by default, which means you have to account for all internally generated columns, if you want to refer to the appropriate column value in your request.

You can change the default assignment of column reference numbers by using the SET CNOTATION=PRINTONLY command which assigns column numbers only to columns that display in the report output. You can use column notation in COMPUTE and RECAP commands to refer to these columns in your request.

When a report is sorted using the ACROSS phrase, all of the retrieved values are aligned under their appropriate columns. Each column has a title consisting of one value of the ACROSS field. The entire column of data can be addressed by this value in a RECAP calculation.

Example: Referring to a Column by Its Value in a RECAP Expression

The following request uses a factor that depends on the value of the ACROSS field (YEAR) to calculate the inventory cost for each year. It then calculates the profit by summing the assets and subtracting the inventory cost for each year.

TABLE FILE LEDGER
SUM AMOUNT ACROSS YEAR
FOR ACCOUNT
10$$ AS 'CASH' LABEL CASH                         OVER
1100 AS 'ACCOUNTS RECEIVABLE' LABEL RECEIVE       OVER
BAR                                               OVER
1200 AS 'INVENTORY VALUE' LABEL INVENT            OVER 
RECAP INVENTORY_FACTOR/F5.2 = IF YEAR LT '1986' 
   THEN 1.1 ELSE 1.25; AS 'INVENTORY COST FACTOR' OVER 
RECAP INVENTORY_COST = INVENTORY_FACTOR * INVENT;
   AS 'INVENTORY COST'                            OVER
BAR                                               OVER
RECAP PROFIT = CASH + RECEIVE - INVENTORY_COST;
END

The output is shown in the following image.