{CALCULATE COLUMN}

Command

The CALCULATE COLUMN command 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.
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.

Default Value

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

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.

Example

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

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, use:
{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," use:
{CALCULATE COLUMN "% of Total" = 12 % "Total Sales" 12}

To calculate a new column equal to column 1 multiplied by the constant 35, use:
{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}

Notes

No more than 499 column calculations can be defined at any one time in the report.
All arguments in expressions must be valid data column numbers, as determined by the original order of the columns, or constants. Floating point constants can be entered directly into an expression (for example 0.05). Integer values are designated by a decimal point following the last digit (for example, 10. ); this distinguishes integer constants from column references. For example, the command sums columns 1 through 12 and divides the total by 12:

     {CALCULATE COLUMN "Average Sales"= 1 : 12 / 12. }

You must precede and follow all operators in an expression with a single space.
Nested (parenthetical) expressions are not supported.
Expressions are always evaluated left to right, regardless of operator precedence. For example, the expression 1 + 4 + 5 / 100.0 sums columns 1, 4, and 5, and divides the total by 100. To sum columns 1 and 4 and add the quotient of column 5 divided by 100, use the following expression: 5 / 100.0 + 1 + 4
You can use the ORDER command to arrange columns in an easy-to-read fashion.
If you use the same name for more than one column, Hyperion Essbase creates only the last column specified in the CALC COLUMN command. Use a leading space with the second (or two leading spaces with the third, and so on) name to create a "unique" column name.
The SUM RANGE operator (:) can only be used as the first operation in an expression. For example, = 1 : 3 or = 1 : 3 + 7 * 9 are valid expressions, but =7* 9 : 12 is invalid because the SUM RANGE operator is not the first operator. The SUM RANGE operator (:) may not be used with a calculated row as one of the arguments. For example, = 1 : "Total_Sales" 3 is invalid.
A reference to a calculated row in a column calculation must include a column restriction to specify the single column whose value is to be used in the calculation.
A column calculation cannot reference a calculated row name that has not yet been declared. Use { CALCULATE ROW "calcrowname" OFF } prior to the CALCULATE COLUMN referencing it, to declare a calculated row's name when the actual definition of the row calculation's operation cannot be done until later in the report.
If a column calculation is attached to a member that is nested within a repeating group, it is redefined over and over. This is allowed, but very inefficient. When possible, define column calculations prior to areas of the report where members repeat. If the same name occurs later in the report with a new and different definition, the prior definition is lost.