Arithmetic Calculations

You can construct arithmetic equations that use values in other columns in the structure browser.

In addition to the calculations defined by the settings listed in Calculation Settings, you can also construct arithmetic equations that use values in other columns in the structure browser.

The following topics are discussed:

Variables

You perform arithmetic calculations using variables that define the column to use and the level (of expansions). Variables use this format:

<ColumnName>[<Level>]

You can use any column name in the structure browser. When defining a level:


  • The value must be => 0; use 0 for root level objects.
  • To identify all cells in the column, use CURRENT (indicates the level of the cell on which the expression is being evaluated).
  • Use LEAF to indicate the last level in the structure.
  • You can include expressions such as CURRENT+1 or LEAF-1.
  • You can include a comma-separated list of levels, such as <ColumnName>[0,1,2,3] or use the not equal operator such as <ColumnName>[!5].
  • Level defaults to CURRENT if not specified.

You can define the label used for aggregate functions, such as SUM or AVG, using <ColumnName>["Label"].

Syntax

An arithmetic calculation uses this syntax:

<LHS>=<RHS>

LHS is Left Hand Side; RHS is Right Hand Side.

RHS is optional; if not provided it defaults to the variable <ColumnName>[CURRENT], or <ColumnName>. When used, RHS must be a cell variable and defines where to put the calculated variable in the structure browser.

LHS can use any arithmetic operations (+ - * / %), arithmetic functions, or aggregation functions and can reference other cell variables. The arithmetic functions include all those supported by the JavaScript Math class. In addition, you can use the string function Concat.

Concat("Bal: ", Jan + Feb + Mar)

The function can call any string or arithmetic expressions.

This table lists the supported aggregation functions:

Function Description Syntax/Examples

SUM

Totals the specified cells.

SUM(Cost[0])

SUM(Shipping Cost[0]],Labor Cost[0])

AVE

Average of the specified cells.

AVE(Cost[0])

AVE(Shipping Cost[0]],Labor Cost[0])

MAX

Maximum value in the specified cells.

MAX(Cost[0])

MAX(Shipping Cost[0]],Labor Cost[0])

MIN

Minimum value in the specified cells.

MIN(Cost[0])

MIN(Shipping Cost[0]],Labor Cost[0])

MEDIAN

Median value in the specified cells.

MEDIAN(Cost[0])

MEDIAN (Shipping Cost[0]],Labor Cost[0])

Examples

Say you want to calculate a Session Total, which is the sum of the values for the columns with names Jan, Feb, and Mar. You could perform this calculation using this expression:

"Jan[CURRENT] + Feb[CURRENT] + Mar[CURRENT]"

or

Session Total[CURRENT]="Jan[CURRENT] + Feb[CURRENT] + 
Mar[CURRENT]"

The results of the arithmetic expression are shown in the Session Total column.

This next example uses parent/child nodes. A structure browser page includes an Organization that can be expanded to show Companies within that organization, and each Company can be expanded to show the Business Units within the company. You can total the cost of all children objects using this expression:

Cost=SUM(Cost[CURRENT+1])

or

Cost[CURRENT]=SUM(Cost[CURRENT+1])

For each company, the Cost column shows the total for all business units; for each organization, the Cost column shows the total for all companies.

To prevent the calculation from being applied to business units (which have no children in this example), you can use this expression:

Cost[0,1]=SUM(Cost[CURRENT+1])

or

Cost[CURRENT,!LEAF]=SUM(Cost[CURRENT+1])

The first expression indicates to execute the expression for all root (organization) and their children (companies), and for no further levels. The second expression indicates to execute the expression for all levels except the leaf (final) level.