Creates a new report column, performs on-the-fly calculations, and displays the calculation results in the newly-created column.
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 ORDER for more information on column numbering and ordering.
Syntax
{ CALCULATE COLUMN "newColumn" = expression }| Parameter | Description |
|---|---|
"newColumn" | New column name enclosed by quotation marks. |
expression | A 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. |
Notes
No more than 50 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 following command sums columns 1 through 12 and divides the total by 12:
{CALCULATE COLUMN "New_Col" = 1+3 / 6+8 % 15 * 100.-"Tot_Row" 3+12} 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, 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.
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,020The following samples demonstrate additional column calculations.
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