The tool supports the interface of Foresight. Analytics Platform 9 or earlier.

Special Functions

Use special functions from the Functions list to specify the expressions for the destination cube coordinates (points).

Operations

Elements for standard operations are the following:

Operation Brief description
+ Addition.
- Subtraction.
* Multiplication.
/ Division.
> Greater than.
< Less than.
>= Greater than or equal to.
<= Less than.
= Equal.

Math

Elements for mathematical operations are the following:

Function Syntax Description
Nz Nz(Term, E) It returns the expression result if the value of source cube coordinate is NoData, otherwise it returns value of the source cube coordinate.
Parameters:
  • Term is the coordinate of the source cube.

  • E is the expression.

Power Power(Term, K) It returns the result of raising a real number to a real power.
Parameters:
  • Term is the coordinate of the source cube.

  • K is the power.

Abs Abs(Term) It returns the absolute value (modulus) of a real number.
Parameter:
  • Term is the coordinate of the source cube.

Trunc Trunc(Term, K) It truncates fractional part of a value to a given accuracy.
Parameters:
  • Term is the coordinate of the source cube.

  • K is the precision.

Round Round(Term, K) It rounds a value to a specified number of decimal places. If K is less then 0, the value is approximated to the defined number to the left of the decimal point. If K equals 0, the value is approximated to the nearest integer value. If K is greater than 0, the value is approximated to the specified number of decimal places to the right of the decimal point.
Parameters:
  • Term is the coordinate of the source cube.

  • K is the number of decimal places, to which a value should be rounded.

Exp Exp(K) It returns the result of raising the number e to a given power.
Parameter:
  • K is the power.

Ln Ln(Term) It returns natural logarithm for a given value.
Parameter:
  • Term is the coordinate of the source cube.

MAvg MAvg(Term1, Term2, Term3, Term4) It calculates mean of the selected elements.
Parameters:
  • Term1, Term2, Term3, Term4 - source cube coordinates.

Boolean

Elements for logical operations are the following:

Function Syntax Description
AND AND Logical AND.
OR OR Logical OR.
NOT NOT Logical negation.
IIF IIF(<condition>,<expression , if condition is true>,<expression , if condition is false>). Conditional operator.
IsNull IsNull(Term, V1, V2) The function returns V1 if the selected cube coordinate (Term) is an empty value (NULL) or there is no record for this coordinate; otherwise the function returns V2.
Parameters:
  • Term is the coordinate of the source cube.

  • V1, V2 are values; the source-cube coordinates may be used as values.

Other

Function Syntax Description
Min Min(Term1, Term2) Selects the least value of two compared elements.
Parameters:
  • Term1, Term2 - source cube coordinates.

Max Max(Term1, Term2) Selects the greatest of two compared elements.
Parameters:
  • Term1, Term2 - source cube coordinates.

MMin MMin(Term1, Term2, …TermN) It selects the minimum value in the array.
Parameters:
  • Term1, Term2, ...TermN - source cube coordinates.

MMax MMax(Term1, Term2, …TermN) It selects the maximum value in the array.
Parameters:
  • Term1, Term2, ...TermN - source cube coordinates.

Shift Shift(Term, P, К) The result of calculating the function is the value of source cube coordinate shifted by date by the specified number of steps.
To use this function, open the Structure page of multidimensional calculation wizard of the cube source for a calendar dimension, open context menu and select the Date Shift (SHIFT) checkbox.
Parameters:
  • Term is the coordinate of the source cube.

  • P - the period: Year, Quart, Month, Day.

  • K - the number of steps in the specified period (negative values are possible).

NoData

NoData(Term, C1, C2)

The function returns the following:
  • The value of defined cube coordinate if a record is defined for this coordinate, and this record is not NULL.

  • The C1 constant if a record is not defined for this cube coordinate.

  • The C2 constant if its defined cube-coordinate value is NULL.

Parameters:

  • Term is the coordinate of the source cube.

  • C1, C2 are the constants.

NOTE. It is important to make the distinction between an empty or null record and a merely undefined record. A record is empty if and only if it is defined but its value is NULL.

Day - It is used to specify period of the Shift function.
Month - It is used to specify period of the Shift function.
Quart - It is used to specify period of the Shift function.
Year - It is used to specify period of the Shift function.
Check Check(Term) This is used to check values by specified coordinate. If the specified coordinate contains empty value (NULL), then the entire formula is not calculated.
Parameter:
  • Term is the coordinate of the source cube.

Working with Sets

Functions are used to work with set of values by a cube coordinate. In order the functions work, it is assumed that the table that is used to build a cube includes one indicator more that the number of dimensions in the cube. Function operand is the coordinate of the cube value which, due to the missing dimension in the cube is considered as a series of values.

NOTE. If coordinates of the source cube used as parameters in functions used to work with sets, are calculated, that is, they have formulas set in the multidimensional calculation, which uses the Consider Dependencies between Formulas checkbox is used, result of the function with the set is 0.

Function Syntax Description
DMin DMin(Term) It selects the least value from the series of values in the cube coordinate.
Parameter:
  • Term is the coordinate of the source cube.

DMax DMax(Term) It selects the greatest value from the series of values in the cube coordinate.
Parameter:
  • Term is the coordinate of the source cube.

DMedian DMedian(Term) It calculates median by values from the series of values in the cube coordinate.
Parameter:
  • Term is the coordinate of the source cube.

DSum DSum(Term) It calculates sum of values from the values in the cube coordinate.
Parameter:
  • Term is the coordinate of the source cube.

DCnt DCnt(Term) It calculates the number of values from the series of values in the cube coordinate.
Parameter:
  • Term is the coordinate of the source cube.

DCntD DCntD(Term) It calculates the number of unique values from the series of values in the cube coordinate.
Parameter:
  • Term is the coordinate of the source cube.

DAvg DAvg(Term) It calculates average of values from the series of values in the cube coordinate.
Parameter:
  • Term is the coordinate of the source cube.

NOTE. Working with sets is supported for the following DBMS: Microsoft SQL Server, Teradata and Oracle.

See also:

Editing Formulas | Multidimensional Calculation on Database Server