All Formulas and Calculations

Use predefined formulas, functions, conditions, and operators to build up a formula in the Modeler or in stories.

Note

Some functions are specific to the modeler and some are specific to calculations in stories.

Remember
You can press Ctrl + Space in the formula bar to display a list of suggestions, or type [ for a list of valid measures and dimensions.
Note
Function names, AND keywords, and time navigation granularities (for example, "year" and "Month") are case-insensitive.
Note
While preparing data in the Data Integration view, you can create a calculated column based on input from another column and the application of a formula expression. For a list of functions available in this scenario see Supported Functions for Calculated Columns.
Mathematical Operators

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

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
Note
These functions are available only in Modeler.
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
Note
These functions are available only in Modeler.
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

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 [SALES] is greater than 100, returns [SALES]
  • If [SALES] is less than or equal to 100, returns [SALES]+10
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:
  • If [SALES] is greater than 100, returns [SALES]
Inverse Functions
Note
These functions are available only in Modeler.
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.

Mathematical Functions

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

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.
  • string: the string on which to perform the replace operation.
  • target: the string that will be replaced.
  • replacement: the string value that will be used to replace target.

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

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 Functions

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