Functions in Calculation formulas for Calculated Measures
Basic Functions in Formulas
| Function | Description |
|---|---|
| ABS(arg1) | Gives the absolute the value of the resulting value of arg1 |
| ROUND(arg1) | Rounds the value of the resulting value of arg1 to the nearest whole number |
| TRUNC(arg1) | Truncates the value of the resulting value of arg1 to a whole number by removing the decimal or fractorial part |
| SQR(arg1) | Squareroot |
Time Series Functions in Formulas
| Function | Description |
|---|---|
| AVERAGE(n, [measure]) | Average of n periods for a Measure in a period range from current period |
| AVERAGE2(n, [measure]) | Average2 of n periods for a Measure in a period range from previous period |
| AVGPOS(<periods>, <expression>) | Returns the average value of expression but considers a positive value only from the current period |
| AVGPOS2(<periods>, <expression>) | Returns the average value of expression but considers a positive value only from the previous period |
| OFFSET(n, arg1) | Resulting value of arg1 from
earlier periods. n must be a negative number of periods. If arg1 is a measure a positive n can be used to retrieve the value of that measure from a later period. |
| SELF(n) | Uses the resulting formula value from earlier period. Typically used for time series. n must be a negative number of periods. |
| SELFNA(n) | Same as SELF (n) except that a Null is set if the earlier value is Null or Empty. |
| ACCUMULATE(start period, periods before reset, [measure]) | Accumulates cell values from a Measure starting from 'start period' and resetting the accumulated value after 'periods before reset'. 'Start period' and 'periods before reset' must be positive whole numbers. |
| STDDEV(n, [measure]) | Standard deviation over n periods for measure |
| STDDEV2(<periods>, <expression>) | Returns the standard deviation from the previous period |
| STDDEVP(n, [measure]) | Same as STDDEV but for population |
| STDDEVPOS2(<periods>, <expression>) | Returns the standard deviation from previous period and considering positive values only |
| VARIANCE(n, [measure]) | Variance of measure over n periods |
| VARIANCE2(<periods>, <expression>) | Returns the variance from the previous period |
| VARIANCEP(n, [measure]) | Same as VARIANCE but for population |
| VARIANCEPOS2(<periods>, <expression>) | Returns the variance from previous period and considering positive values only |
| SUM(<periods>, <expression>) | Gets the sum of an expression over a number of cells. The decimal fraction of the current period is included if period is a decimal number |
| POSORNULL(<expression>) | Returns the value of expression if greater then 0 else NULL |
| POSORZERO(<expression>) | Returns the value of expression if greater then 0 else 0 |
| CAPSENSE(<expression measure A>, ValueDown, ValueUp, PctDown, PctUp, <expression measure B>) |
The value cannot be negative. The maximum of ValueDown and PctDown is used if B>A. The maximum of ValueUp and PctUp is used if B<A. A is returned if all parameters ValueDown or ValueUp and PctDown or PctUp is 0, that is, without capping. Returns the capped value of A with maximum of values and Pct of B |
| CAPVALUE(<Min expression>, <Max expression>, <measure expression>) | Returns the minimum or maximum value else
measure expression value if inside min and max If measure expression is NA, then result is the min expression value |
| CAPVALUENA | Same as CapValue but returns to NA (empty cell) if measure is NA |
| MIN(<expression A>, <expression B>) | Returns the minimum value of A and B. If both are NA, then 0 is returned. |
| MAX(<expression A>, <expression B>) | Returns the maximum value of A and B. If both are NA, then 0 is returned. |
| QUARTILE(<type>, <quart>, <expression>, <periods>) | Returns the quartile value of expression
from current period. QUARTILE2 is similar from the previous period.
|
| ISOUTLIER(<A expression>, <type>, <scale>, <B expression>, <periods>) |
Returns 1 if value A expression is outside the interquartile range (Q3-Q1) factored by scale, else empty is returned. ISOUTLIER2 is similar from the previous period.
|
| MEDIAN(<expression>, <periods>) |
Median is the middle data point in a sorted sequence. If series have even number of periods, then it is the average of the two middle points. MEDIAN2 is similar from the previous period. Returns the median value or empty |
Condition Functions in Formulas
| Function | Description |
|---|---|
| EQ(arg1, arg2) | arg1 = arg2 give 1 else 0 |
| NE(arg1, arg2) | arg1 <> arg2 give 1 else 0 |
| GT(arg1, arg2) | arg1 > arg2 give 1 else 0 |
| GE(arg1, arg2) | arg1 >= arg2 give 1 else 0 |
| LT(arg1, arg2) | arg1 < arg2 give 1 else 0 |
| LE(arg1, arg2) | arg1 <= arg2 give 1 else 0 |
Special Functions in Formulas
| Function | Description |
|---|---|
| CHANGES([measure]) | Displays the change part of
the measure It extracts the change part of any changeable measure for display in its own column or to be used with other expressions in a complex formula. |
| MFOR([Tot Fcst]) | Displays the manual component
of the special Total Forecast measure. The MFOR(<measure>) function are only present if the Favorite View includes a TotalFcst datatype (4). The function MFOR (Manual FORecast (null or related to forecast; separate column utility) ) is intended to make it possible to create a column (CM) that will display only the cells that have manual overridings to the calculated forecast. |
| DISPLAY(n, arg1, arg2) | Displays the resulting values from arg1 up to n periods and result from arg2 after n periods (where n must be a positive whole number) |
| IIF(condition, arg1, arg2) | Arg1 is evaluated if condition is true ( <> 0 ) else Arg2. |
| NORMSINV(arg1) | Arg1 is between 0 and 1. The NORMSINV(arg1) function is the Inverse Normal Distribution. arg1<=0 returns -10 and arg1>=1 returns=10. NORMSINVNA is the same function but returns NULL for arg1<=0 or arg1>=1. |
| P(arg1) | Function P returns the current
period number in the memory model (starting from 0 and continues across
years). Arg1 offsets the Period. In a Favorite view with months for two years, the result of P(1) will be 1 to 24 and P(0) will be 0 to 23 |
| ISNULL([measure]) | Returns 1 if measure is Null else 0 |
| ZN([expression]) | Returns 0 if expression is Null else the value of the expression |
| ZIIF(condition, arg1, arg2) | Arg1 is evaluated if the condition is <> 0 else Arg2. If the condition is Null, Arg2 is evaluated. |