All Formulas and Calculations
Use predefined formulas, functions, conditions, and operators to build up a formula in the Modeler or in stories.
Some functions are specific to the modeler and some are specific to calculations in stories.
These are the available groups of functions:
These operators can be used either with constants or when referring to members. For example, [A1000]/3 is the value of account member A1000 divided by 3.
Mathematical Operators | Syntax | Meaning | Example |
---|---|---|---|
Addition | N1+N2 | Adds two numbers. Note If a summing formula in a table includes a row with no data,
the sums won't appear, because when a null value is added to
other values, the result is null.A workaround is to use the SUM keyword. For example, if the values of A1, A2, and A3 are 50, null, and 100: A1+A2+A3 = null SUM(A1:A3) = 150 |
42 + 1337 returns 1379 |
Subtraction | N1-N2 | Subtracts the second number from the first one. | 1337 - 42 returns 1295 |
Multiplication | N1*N2 | Multiplies two numbers. | 4 * 6 returns 24 |
Division | N1/N2 | Divides the first number by the second one. | 12 / 3 returns 4 |
Power | N1**N2 | Returns the first number raised to the power of the second number. | 2 ** 6 returns 64 |
Parentheses | () | Changes the order of execution of the operators. | (2 + 3) * 4 returns 20 2 + (3 * 4) returns 14 |
Unary Minus | -N1 | Changes the sign of the following number. | -3 returns -3 5 + -3 returns 2 |
Conditional Operators | Syntax | Meaning | Example |
---|---|---|---|
AND | and | Takes two Booleans. Returns true if both values are true; otherwise false. |
false and true returns false 1 = 1 and 2 = 2 returns true |
OR | or | Takes two Booleans. Returns true if one or both values are true; otherwise false. |
false or true returns true 1 = 1 or 2 = 3 returns true 1 = 2 or 2 = 3 returns false |
Equal | = | Compares two numbers or other values. Returns true if the values are equal. |
1 = 1 returns true 1 = 2 returns false |
Greater Than | > | Compares two numbers or other values. Returns true if the first value is greater than the second one. |
1 > 1 returns false 2 > 1 returns true |
Less Than | < | Compares two numbers or other values. Returns true if the first value is less than the second one. |
1 < 2 returns true 1 < 0 returns false |
Greater Than or Equal | >= | Compares two numbers or other values. Returns true if the first value is greater than or equal to the second one. |
1 >= 1 returns true 2 >= 1 returns true 1 >= 2 returns false |
Less Than or Equal | <= | Compares two numbers or other values. Returns true if the first value is less than or equal to the second one. |
1 <= 1 returns true 1 <= 2 returns true 1 <= 0 returns false |
Not Equal | != | Compares two numbers or other values. Returns true if the values are not equal. |
1 != 1 returns false 1 != 2 returns true |
ISNULL | ISNULL() |
Checks for a Null value. Returns true if a field contains a Null value. Adding NOT to the function reverses the results. |
IF(ISNULL(sale), T, F) returns T when the sale value is blank ( or null), and F when there is a sale value. IF(NOT(ISNULL(sale)), T, F) returns T when there is a sale value, and F when the sale value is blank. |
Like | Like(string, string) |
Determines whether a character string matches a specified pattern. Code Syntax like("matchExpr","pattern") matchExpr: The string expression to search. pattern: The pattern string constant to search for. The pattern can include regular characters and the following special characters: "." matches a single character ".*" matches zero to many characters Returns true if the pattern string matches the macthExpr. |
Like("Hiking is fun", "H.* is .un") returns true |
Business Functions | Syntax | Meaning | Example |
---|---|---|---|
Compound Annual Growth Rate (CAGR) | CAGR([<account member>], <start year>, <end year>) | The mean annual growth rate over a specified period of time
longer than one year. <start year> and <end year> can be a numeric year enclosed in quotation marks: "2013", or a year variable enclosed in brackets and apostrophes: ['year2']. Note: the start year must be less than or equal to the end year. |
CAGR([NetRevenue],"2014","2016") returns the Compound Annual Growth
Rate of the account NetRevenue over the two-year period spanning the
end of 2014 to the end of 2016. newCAGR = CAGR([400000],"2013",['year2']) |
Simple Moving Average (SMA) | SMA([<account member>],[d/<date dimension>], "<time granularity>", <period>) | The sum of the values of a member for a number of time periods, divided by the number
of time periods. The parameter <period> can be any positive integer. |
SMA([NetRevenue],[d/Date],"Year",2) returns the Simple Moving Average
of the account NetRevenue using the last two years. newSMA = SMA([400000],[d/Date],"Month",3) |
Year Over Year (YoY) | YoY([<account member>]) | The percentage difference between the value of a member in the current year compared with the previous year. | Revenue_YoY = YoY([Revenue]) For a detailed example, see YoY Formula Details. |
Lookup and Reference Functions | Syntax | Meaning | Example |
---|---|---|---|
Link | Link([<linked model name>], [<linked member>], <POV>) | Model linking (blending) can be used to display data from more than one model in a
single story. Model linking means adding facts (actual values) from
Model A into Model B, by linking to the facts in Model A. For example, you may have one model that contains sales quantities for some products, and a second model that contains product prices. If you want to calculate revenue, you could bring the prices from the second model into the first model, by adding a new measure, which includes the Link formula, to the first model. Note: the Link formula cannot be used with other formulas. |
For detailed syntax information, with examples, see Link Formula Details. |
Lookup | LOOKUP([<account member>], [<POV>], [<Ignore Dimension>]) | Returns a value by referring to an account, filtering on dimension and member pairs.
Optionally, specific dimensions that should be ignored can be
specified. The dimensions defined in the Lookup formula do not need to be in the current drill state. Multiple dimension member pairs can be listed in the POV expression. If the second parameter is specified, then the third parameter is optional. If the third parameter is specified, then the second parameter is optional. The Lookup function supports one or more dimension attributes or properties. |
For detailed syntax information, with examples, see Restrict and Lookup. Also see ResultLookup vs. Lookup. |
Restrict | RESTRICT([<account member>], [<POV>]) | Returns a measure value restricted to an account and a list of dimension
members. The functionality of the Restrict and Lookup formulas is very similar. They differ in the way the results are displayed, showing a breakdown of aggregated numbers. The Restrict formula shows no value for rows or columns where no data has been selected. The Restrict function supports one or more dimension attributes or properties. |
For detailed syntax information, with examples, see Restrict and Lookup. |
ResultLookup | ResultLookup([<measure name>], [d/<dimension name>]="<dimension value>" and [d/<dimension name>]="<dimension value>"...) | Returns a cell value based on an account name and a list of dimension members. For example, ResultLookup([Sales], [d/Region]="CA") returns the value of Sales for the Region "CA". The ResultLookup function supports one or more dimension attributes or properties. Note
The ResultLookup function uses post-aggregation values. Also, only the data that is available in the grid is accessible using this function. For the same reason, value driver tree nodes based on ResultLookup functions may not display any data. |
ResultLookup([NetRevenue],[d/City]="Istanbul") returns the cell value
of NetRevenue where City is equal to
Istanbul. ResultLookup([NetRevenue],[d/ResponsibilityCenter].[p/currency]="CAD") returns the cell value of NetRevenue where the Currency property of the ResponsibilityCenter dimension is equal to Canadian dollars, provided that the Currency property is part of the drill state. ResultLookup([Sales], [d/Date]="2010" and [d/Region]="Greenland") IF([Sales] > ResultLookup([Sales], [d/Date]="2010"), [Profit] * 2, [Profit]) Also see ResultLookup vs. Lookup. |
Logical Function | Meaning | Example |
---|---|---|
IF(condition, then, else) | The IF function returns the first value <then> if the specified condition is
TRUE, and the second value <else> if the condition is
FALSE. The IF function can also be used with Boolean values. For example, if you wanted to treat cells with NULL values as cells with zero (0) values, you could have the following formula: IF(EQUAL(B10,NULL), 0, B10) |
IF([SALES)>100, [SALES], [SALES]+10) returns
the following:
|
IF(condition, then) |
The IF function returns a value if the specified condition is TRUE. Data points that don't match the condition will be assigned a NULL value. Note This syntax is not supported for live data models. |
IF([SALES)>100, [SALES]) returns the following:
|
Syntax | Meaning | Example |
---|---|---|
| INVERSE ([<target member 1>]:= Formula 1) OR INVERSE ([<target member 2>]:= Formula 2) |
Add one or more inverse formulas to the end of a formula to specify how the formula should be reversed. This function allows data entry on the formula in a story. The number of inverse formulas is limited to the number of operands in the base formula for which the inverse formulas are applied. Multiple inverse formulas are separated by the keyword OR. |
For detailed syntax information with examples, see Inverse Formulas. |
| INVERSEIF (<condition>, [<target member>]:= Formula ) |
This type of inverse formula is only applied when the specified condition is TRUE. Otherwise, it functions similarly to the INVERSE function. INVERSE and INVERSEIF functions can both be added to the same formula. |
For detailed syntax information with examples, see Conditional Inverse Formulas. |
These functions can be used with either constants or account members:
Mathematical Functions | Meaning | Example |
---|---|---|
%GrandTotal(Account) | Returns the percentage of the grand total that each value represents. | %GrandTotal(Sales) returns the percentage of the grand total that each value represents. |
ABS(number) | Returns the absolute value of a number (the number without its sign). | ABS(-11) returns 11 |
EXP(number) | Natural exponential function. Returns the value of e (2.718) raised to a power. | EXP(3) returns 20.0855 |
GrandTotal(Account) | Returns the grand total of all the Account values in the result set. Filters are included in the calculation of the grand total. | GrandTotal(Sales) returns the aggregated value of Sales. |
LOG(number) | Returns the natural logarithm. | LOG(100) returns 4.605 |
LOG10(number) | Returns the base 10 common logarithm. | LOG10(100) returns 2 |
MAX(number1, number2, ...) | Returns the largest of two or more numbers. | MAX(10,20,15) returns the value 20 |
MIN(number1, number2, ...) | Returns the smallest of two or more numbers. | MIN(10,20,15) returns the value 10 |
MOD(number1, number2) | Returns the remainder of dividing a specified number <number1> by another specified number <number2>. | MOD(15,2) returns the value 1 |
POWER(number, power) | Returns the result of a number raised to a power. | POWER(2,3) returns the value 8 |
SQRT(number) | Returns the square root of a number. | SQRT(4) returns 2 |
String Functions | Meaning | Example |
---|---|---|
FINDINDEX(string, string, startIndex ) |
Searches for a substring and returns its 0-based start index. Returns -1 if the target is not found. |
FINDINDEX('This is only a string.', 'is', 2) returns 2 FINDINDEX('This is only a string.', 'is', 3) returns 5 FINDINDEX('This is only a string.', 'is', 6) returns -1 |
SubString(string, startIndex, length) | Returns a substring (of a specific length) for the specified string. |
SubString('Monday', 3, 3) returns “day” |
TRIM(string) | Returns a copy of a specified string, with leading and trailing spaces removed. |
TRIM(' cloud ') returns “cloud” |
Length(str) | Returns the length of a string. |
Length('How Long') returns 8 |
CONCAT(string, string ) | Concatenates one specified string with another specified string to form a single string. |
CONCAT('ABC', 'DE') returns “ABCDE” |
SPLIT(string, delimiter, index ) | Returns a substring from a string, using a case-sensitive delimiter character. (The delimiter character must be a single character.) |
SPLIT('This-is-only-a-string', '-', 3) returns “only” |
RIGHT(string, length) | Returns the specified number of characters from the end of the string. |
RIGHT('This is only a string.', 7) returns “string.” |
LEFT(string, length) | Returns the specified number of characters from the start of the string. |
LEFT('This is only a string', 4) returns “This” |
REPLACE(string, target, replacement) | For a specified string, returns a string with all occurrences
of a specified target string replaced with
another specified string replacement.
|
REPLACE('Mexico DF', 'DF' , 'City') returns “Mexico City” |
LOWERCASE(text) | Returns lowercase of a text value. |
LOWERCASE('AbC') returns “abc” |
UPPERCASE(text) | Returns uppercase of a text value. |
UPPERCASE('AbC') returns “ABC” |
ENDSWITH(string, string ) | Returns true (1) if the string ends with the specified substring. It is case-sensitive, and doesn't ignore trailing white spaces. | ENDSWITH('This ends with this.', 'this.') returns 1 ENDSWITH('This ends with this.', 'This.') returns 0 |
Conversion Functions | Meaning | Example |
---|---|---|
CEIL(number1, number2) | Returns the smallest number that is greater than or equal to a specified number
<number1>. The optional <number2> argument specifies the number of decimal places. |
CEIL(14.8) returns the value 15 CEIL(14.82,0) returns the value 15 CEIL(14.82,1) returns the value 14.9 CEIL(14.82,-1) returns the value 20 |
DECFLOAT(string) | Converts string to a decimal floating point number. | DECFLOAT("14.6") returns 14.6 |
DOUBLE(arg) | Converts arg to a high precision floating point number. | DOUBLE(14) returns 14.0 |
FLOAT(arg) | Converts arg to a floating point number. | FLOAT(14) returns 14.0 |
FLOOR(number1, number2) | Returns the largest number that is not greater than <number1>. The optional <number2> argument specifies the number of decimal places. |
FLOOR(14.8) returns the value 14 FLOOR(14.82,0) returns the value 14 FLOOR(14.82,1) returns the value 14.8 FLOOR(14.82,-1) returns the value 10 |
INT(number) | Returns the integer portion of a number. | INT(9.5) returns the value 9 INT(-9.5) returns the value -9 |
ROUND(number1, number2) | Rounds argument <number1> to the specified number <number2> of decimal places. | ROUND(14.82,1) returns the value
14.8 ROUND(14.82,0) returns the value 15 ROUND(14.82,-1) returns the value 10 |
TRUNC(number1, number2) | Returns a specified numeric value <number1>, truncated to a specified number <number2> of decimal places. | TRUNC(12.281,1) returns the value 12.2 |
Date and Time Function | Meaning | Example |
---|---|---|
Datediff(Date1, Date2, granularity) | Returns the number of years, months, or days between two dates. For example, you can
use the Datediff function if you want to use a calculation like
this: ShipDate - OrderDate = Number of days Note
The date dimensions used as arguments in the function are automatically added as exception aggregation dimensions. The default exception aggregation type is automatically set to AVG. |
DATEDIFF([d/ShipDate], [d/OrderDate], "Day") returns the number of
days between the ship date and the order
date DATEDIFF([d/ShipDate], "2018-07-01", "Day") returns the number of days between the ship date and July 1, 2018 |