CALCULATE COLUMN

Creates a new report column, performs on-the-fly calculations, and displays the calculation results in the newly-created column.

Syntax

{ CALCULATE COLUMN "newColumn" = expression }

"newColumn" New column name enclosed by quotation marks.
expression Any valid column calculation expression.

If an operation or equation is not specified, the default is + (add).

The following mathematical operators are supported in column calculations:

+  Addition operator.

-  Subtraction operator.

*  Multiplication operator.

%X%Y  Evaluates X as a percentage of Y.

/  Division operator.

:X:Y  Performs a summation of data values from X to Y (inclusive). Must be the first operator if used with multiple operators.

Description

CALCULATE COLUMN adds up to 499 ad-hoc column calculations to a report (the maximum number of column calculations that can be defined at any one time).

Each new calculated column is appended to the right of the existing columns in the order in which it is created, and is given the next available column number.

See the ORDER command for more information on column numbering and ordering.

Notes

Example

Example 1

The following example is based on Sample Basic.

<PAGE (Measures, Market)
Sales
<SYM
     <COLUMN (Scenario, Year)
     Actual Budget
     Jan Feb
{WIDTH 8 0}
{WIDTH 7}
{WIDTH 11 5 6}
{CALCULATE COLUMN "Actual YTD" = 1 + 2}
{CALCULATE COLUMN "Budget YTD" = 3 + 4}
{ORDER 0 1 2 5 3 4 6}
<ROW (Market)
<CHILD "400"
     !

This example produces the following report:


                        Sales    Market
                Actual                   Budget         
           Jan    Feb Actual Ytd    Jan    Feb Budget Ytd 
        ====== ====== ========== ====== ====== ========== 
400-10   2,839  2,879      5,718  2,320  2,350      4,670 
400-20   2,562  2,596      5,158  2,040  2,050      4,090
400-30   1,233  1,261      2,494    990  1,030      2,020

Example 2

The following samples demonstrate other column calculations that you can perform.

To calculate a new column named "1st Qtr" equal to the sum of the first 3 columns:

{CALCULATE COLUMN "1st Qtr" = 1 : 3}

To calculate a new column that is equal to column 12 taken as a percentage of the value in column 12 of a calculated row called "Total Sales":

{CALCULATE COLUMN "% of Total" = 12 % "Total Sales" 12}

To calculate a new column equal to column 1 multiplied by the constant 35:

{CALCULATE COLUMN "Extended_Price" = 1 * 35.}

The following example calculates a new column, adds column 1 to column 3, divides the result by column 6, adds column 8, takes that result as a percentage of column 15, multiplies that result by the constant number 100, subtracts the value from the 3rd column of the calculated row "Tot_Row", and adds the result to column 12.

{CALCULATE COLUMN "New_Col" = 1+3 / 6+8 % 15 * 100.-"Tot_Row" 3+12}

See Also

OFFCOLCALCS
ONCOLCALCS
REMOVECOLCALCS
SETROWOP