When defining a formula or calculated key figure, you can use the following data functions as operators.
Operator |
Operator Name |
Description |
---|---|---|
COUNT |
Counter |
COUNT(<Expression>) Returns value 1 if the expression <> identified by <Expression> results in 0, otherwise 0. |
DATE |
Casting Operator Date |
DATE The value is given as the number of days starting from 01.01.0000 and then displayed as a date. The value can be between 0 and a million. This which allows for a date range between 01.01.0000 and 27.11.2738. |
DELTA |
Delta |
DELTA(<Expression>) Returns value 1 if the expression identified by <Expression> results in 0, otherwise 0. Caution
This function is identical to the NOT operator and should not be used anymore. |
FIX | FIX | Fixes the context of the aggregation by preventing the exception aggregation from using the GROUP-BY clause. The constant selection is therefore made before the formula calculation (see Fix Operator). |
NDIV0 |
No DIV0 |
NDIV(<Expression>) Results in 0 if the expression identified by <Expression> is caused by a division by 0 during calculation. Otherwise, the result is the value of the expression. This is used in order to avoid error messages or so that you can continue calculating with a defined result. |
NODIM |
Value without Units or Currencies |
NODIM(<Expression>) Returns purely numerical values of <expression> and suppresses units and currencies. |
NOERR |
No Error |
NOERR(<Expression>) Results in 0 if the calculation of <expression> causes an arithmetical error. Otherwise, the result is the value of the expression. This is used in order to avoid error messages or so that you can continue calculating with a defined result. |
SUMCT |
Norming Operation Result |
SUMCT <Operand> Returns the result of the operand at the next-highest level (intermediate result) (see Percentage Functions, %CT). |
SUMGT |
Norming Operation Grand Total |
SUMGT <Operand> Returns the grand total of the operand (see Percentage Functions, %GT). |
SUMRT |
Norming Operation Query Result |
SUMRT <Operand> Returns the query result of the operand (see Percentage Functions, %RT). Unlike the process for calculating the overall result, dynamic filters are not used in the calculation of the query result. This means that the calculation always normalizes to the same value, irrespective of the filter status and the navigational state. |
TIME |
Casting Operator Time |
TIME The TIME operator takes any key figure or (partial) formula and makes a key figure of type TIME. The system removes the decimal places, interprets the value as seconds, and returns the value in the form +-hh:mm:ss. Example
-4812 is thus displayed as -1:20:12. A number is expressed as the number of seconds from 00:00:00 and is expressed in hours, minutes, and seconds. A key figure of type TIME with value 0 is displayed as 00:00:00. Negative times are allowed: For a negative number the system calculates backwards from 00:00:00. You can enter any number of hours. After 24 hours, the system does not restart again at 0. |
NOERR (x): Equal to 0 for undefined calculations, otherwise x
NOERR(SQRT(-1)) = 0
Result (SUMCT)
Year |
Region |
Revenue |
SUMCT 'Revenue' |
---|---|---|---|
1999 |
North |
30 |
60 |
South |
30 |
60 |
|
Result 1999 |
60 |
180 |
|
2000 |
North |
60 |
120 |
South |
60 |
120 |
|
Result 2000 |
120 |
180 |
|
Overall Result |
180 |
180 |
Grand Total (SUMGT)
Year |
Region |
Revenue |
SUMGT 'Revenue' |
---|---|---|---|
1999 |
North |
30 |
180 |
South |
30 |
180 |
|
Result 1999 |
60 |
180 |
|
2000 |
North |
60 |
180 |
South |
60 |
180 |
|
Result 2000 |
120 |
180 |
|
Overall Result |
180 |
180 |
Query Result (Report Total) (SUMRT)
Year |
Region |
Revenue |
SUMRT "Revenue" |
---|---|---|---|
1999 |
North |
30 |
180 |
South |
30 |
180 |
|
Result 1999 |
60 |
180 |
|
Overall Result |
60 |
180 |
The year 1999 is in the dynamic filter. The missing revenue 120 comes from the year 2000 (see SUMGT).
Process Value as Time (TIME)
This function is mainly intended for offset calculations. If you want to calculate 4 hours for time key figures for example, you can use the TIME operator.