All Formulas and Calculations

Note

Some functions are specific to the Modeler and some are specific to formulas that can be created in the calculation editor in stories.

Formulas

These are the available groups of functions:

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`

Note

Comparisons using operators like Greater Than or Less Than with null as one operand will always evaluate to false.

Business Functions

Note

These functions are available only in the Modeler.

Business Functions	Syntax	Meaning	Example
Compound Annual Growth Rate (CAGR)	Account Models `CAGR([<account member>], [d/<date dimension>], <start year>, <end year>)` New Model Type `CAGR([<measure>], [d/<date dimension>], <start year>, <end year>)`	The mean annual growth rate over a specified period of time longer than one year. This function applies to both classic account models and the new model type. `<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. We recommend switching the scaling parameter to percentage manually. Restriction For formulas that use week-level granularity time dimensions, the year must be an integer value.	`CAGR([NetRevenue],[d/<date], "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'])` `CAGR([Quantity],[d/<date], "2017","2022")` returns the CAGR of the measure Quantity over a five-year period spanning the end of 2017 to the end of 2022.
Iterate	`ITERATE([<formula>], [<base member>], [dimensions>])`	Calculates a value iteratively along one or multiple dimensions, based on the value calculated in the previous iteration. You can nest the PRIOR formula to retrieve the value that was calculated for the previous member.	`ITERATE(PRIOR () + [Profit], [Profit], [d/Date].[p/Year])` calculates the running sum over the Profit account along the date dimensions over each year. The value for the first date member of each year is the same as Profit, the value for every following member is calculates by adding the value from the previous member to the value of Profit. For more detailed example, see Iterate Formula Details.
Simple Moving Average (SMA)	Account Models `SMA([<account member>],[d/<date dimension>], "<time granularity>", <period>)` New Model Type `SMA([<measure>],[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. This function applies to both classic account models and the new model type. 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)` `SMA([Quantity],[d/Date],"Year",3)` ) returns the Simple Moving Average of the measure Quantity using the last three years.
Year Over Year (YoY)	Account Models `YoY([<account member>],[d/<date dimension>])` New Model Type `YoY([<measure>],[d/<date dimension>])`	The rate showing the difference between the value of a member in the current year compared to the previous year. This function applies to both classic account models and the new model type. We recommend switching the scaling parameter to percentage manually.	`Revenue_YoY = YoY([Revenue])` For a detailed example, see YoY Formula Details.

Lookup and Reference Functions

Note

These functions are available only in the Modeler.

Lookup and Reference Functions	Syntax	Meaning	Example
AccountLookup	`ACCOUNTLOOKUP([<measure name>],[<account member>])`	Returns a cell value based on a measure name filtered by an account dimension. Note This function is only available with the new model type.	For detailed syntax information, with examples, see Restrict and Lookup.
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.
MeasureLookup	`MEASURELOOKUP([<account member>],[<measure name>])`	Returns a cell value based on an account dimension filtered by a measure name. Note This function is only available with the new model type.	For detailed syntax information, with examples, see Restrict and 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. Restriction ResultLookup is not supported for non-standard time periods such as week-level granularity and user-managed time dimensions. ResultLookup functions only work when the member has a single parent. If the member is present under multiple parents, the formula is invalid.	`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]

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]

Restriction

The IF function is not supported for non-standard time periods such as week-level granularity and user-managed time dimensions.
IF functions only work when the member has a single parent. If the member is present under multiple parents, the formula is invalid.

Inverse Functions

Note

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

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.

Restriction

These functions can be used to create formulas in the Calculation Editor in stories; they can't be used in formulas created directly in a table widget.

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.

Subtotal

Returns the subtotal of a member broken down by one or multiple dimensions.

This function applies to both classic account models and models with measures. Make sure to use the relevant syntax depending on the model type and refer to an account member for a classic account model, or a measure for the new model type.

We recommend selecting the preferred scaling manually in the Formatting panel.

Account Model Syntax

SUBTOTAL (Account Member, Dimension1, ..., Dimension10)

New Model Type Syntax

SUBTOTAL (Measure, Dimension1, ..., Dimension10)

Note

The function does not aggregate values over non-aggregable dimensions (e.g. Account dimension, Category/Version dimension) and generates a separate sub total for each dimension member of these dimensions.
The function supports up to three hierarchical dimensions.
The function supports up to 10 dimensions.
You cannot nest multiple subtotal formulas.

SUBTOTAL([Sales], [d/Location]) returns the aggregated value of Sales broken down by Location.

Region	Location	Sales	Sub Total(Sales)
NA	US	30	60
	CA	30	60
EU	DE	10	40
	FR	20	40
	IT	10	40

%Subtotal

Returns the percentage of subtotal for a member broken down by one or multiple dimensions.

This function applies to both classic account models and models with measures. Make sure to use the relevant syntax depending on the model type and refer to an account member for a classic account model, or a measure for the new model type.

We recommend selecting the preferred scaling manually in the Formatting panel.

Account Model Syntax

%SUBTOTAL (Account Member, Dimension1, ..., Dimension10)

New Model Type Syntax

%SUBTOTAL (Measure, Dimension1, ..., Dimension10)

Note

The function does not aggregate values over non-aggregable dimensions (e.g. Account dimension, Category/Version dimension) and generates a separate sub total for each dimension member of these dimensions.
The function supports up to three hierarchical dimensions.
The function supports up to 10 dimensions.
You cannot nest multiple subtotal formulas.

%SUBTOTAL([Sales], [d/Location]) returns the percentage value of Sales broken down by Location.

Region	Location	Sales	%Sub Total(Sales)
NA	US	30	50%
	CA	30	50%
EU	DE	10	25%
	FR	20	50%
	IT	10	25%

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. Restriction When using INT, keep your values between -2,147,483,648 and 2,147,483,647.	`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