Referring to Columns in Calculations

In this section:

An FML report can refer to explicit columns, as well as explicit rows. You can refer to columns using:

Example: Applying Column Declarations in RECAP Expressions

The following request generates an FML matrix with four rows and three columns of data.

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'
COMPUTE CHANGE/I5C = CUR_YR - LAST_YR;
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' 
END

Both the columns of the report, as well as the cells of the matrix, can be referenced in another FML report.

The output is shown in the following image.

For example, you could use the value 6,499 in another FML report by referring to column 2, row 3. For more information, see Referring to Cells in Calculations.

Referring to Column Numbers 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.

Referring to Contiguous Columns in Calculations

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.

Referring to Column Addresses in Calculations

How to:

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.

Syntax: How to Use Column Addressing in a RECAP Expression

The left-hand side of the expression has the form:

value(s,e,i)[/format]=

where:

value

Is the name you assign to the result of the RECAP calculation.

s

Is the starting column.

e

Is the ending column (it may be * to denote all columns).

i

Is the increment factor.

format

Is the USAGE format of the calculated value. The default value is the format of the original column.

Example: Applying Column Addressing in a RECAP Expression

In the following statement, there are two columns for each month:

SUM ACTUAL AND FORECAST ACROSS MONTH

If you want to perform a calculation only for the ACTUAL data, control the placement of the results with a RECAP in the form:

RECAP calcname(1,*,2)=expression;

The asterisk means to continue the RECAP for all odd-numbered columns (beginning in column 1, with an increment of 2, for all columns).

Referring to Relative Column Addresses in Calculations

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.

Applying Relative Column Addressing in a RECAP Expression

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.

Controlling the Creation of Column Reference Numbers

How to:

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.

Syntax: How to Control the Creation of Column Reference Numbers

SET CNOTATION={ALL|PRINTONLY|EXPLICIT}

where:

ALL

Assigns column reference numbers to every column in the internal matrix. ALL is the default value.

PRINTONLY

Assigns column reference numbers only to columns that display in the report output.

EXPLICIT

Assigns column reference numbers to all fields referenced in the request, whether displayed or not.

Note: CNOTATION is not supported in an ON TABLE phrase.

Referring to Column Values in Calculations

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.