The tool supports the interface of Foresight. Analytics Platform 9 or earlier.
Use special functions from the Functions list to specify the expressions for the destination cube coordinates (points).
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. |
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:
|
Power | Power(Term, K) | It returns the result of raising a real number to a real power. Parameters:
|
Abs | Abs(Term) | It returns the absolute value (modulus) of a real number. Parameter:
|
Trunc | Trunc(Term, K) | It truncates fractional part of a value to a given accuracy. Parameters:
|
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:
|
Exp | Exp(K) | It returns the result of raising the number e to a given power. Parameter:
|
Ln | Ln(Term) | It returns natural logarithm for a given value. Parameter:
|
MAvg | MAvg(Term1, Term2, Term3, Term4) | It calculates mean of the selected elements. Parameters:
|
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:
|
Function | Syntax | Description |
Min | Min(Term1, Term2) | Selects the least value of two compared elements. Parameters:
|
Max | Max(Term1, Term2) | Selects the greatest of two compared elements. Parameters:
|
MMin | MMin(Term1, Term2, …TermN) | It selects the minimum value in the array. Parameters:
|
MMax | MMax(Term1, Term2, …TermN) | It selects the maximum value in the array. Parameters:
|
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:
|
NoData | NoData(Term, C1, C2) |
The function returns the following:
Parameters:
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:
|
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:
|
DMax | DMax(Term) | It selects the greatest value from the series of values in the cube coordinate. Parameter:
|
DMedian | DMedian(Term) | It calculates median by values from the series of values in the cube coordinate. Parameter:
|
DSum | DSum(Term) | It calculates sum of values from the values in the cube coordinate. Parameter:
|
DCnt | DCnt(Term) | It calculates the number of values from the series of values in the cube coordinate. Parameter:
|
DCntD | DCntD(Term) | It calculates the number of unique values from the series of values in the cube coordinate. Parameter:
|
DAvg | DAvg(Term) | It calculates average of values from the series of values in the cube coordinate. Parameter:
|
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