Reference: Operators and Functions for Calculations
Syntax Rules
-
Accounts and calculations and custom metrics. Required. Use the account code (not name) or use the calculation code in expressions.
-
Must be enclosed in brackets. For example,
[1000_Assets]
or[My_Quick_Ratio]
.
-
-
Versions. Optional. Use this syntax:
[code](version = [version name])
to reference any version on an account, calculation, or custom metric defined in your instance. For example:[headcount_FTE](version = [Forecast 2018])
, where Headcount_FTE is the account and Forecast 2018 is the version. If none is specified, the default version is used. See Concept: Calculations in Charts for more on Versions and Order Precedence.-
Must be enclosed in parentheses.
-
Brackets are required when the version name has spaces. For example, Working Budget must be enclosed in brackets. WorkingBudget does not require brackets.
-
-
Operator precedence: Evaluates left to right or enclose in parentheses to calculate first.
Mathematical Operators
Comparison Operators
Use comparison operators with the iif function. For example, iif([30_Net_Income] <= ([41_laptopRev] + [42_desktopRev] + [43_tabletRev]), [30_Net_Income], 40_Product_Revenue])
Comparison Function
Function | Description | Example |
---|---|---|
iif(comparison, true expression, false expression) | Evaluates the comparison expression. If true, returns the true expression. Otherwise returns the false expression . | iif([metric1] > 10 AND [metric1] < 20, [metric2], [metric3]) |
Calculation Functions
Validation Functions
Function | Description | Example |
---|---|---|
isnull(expression, null expression) | Checks if expression is null. If true, returns the null expression. Otherwise returns the expression . | isnull([metric1], [metric2]) |
isinfinity(expression, infinity expression) | Checks if expression is infinity (divide d by zero). If true, returns the infinity expression. Otherwise returns the expression value. | isinfinity([metric1], [metric2]) |
isnan(expression, NaN expression) | Checks if expression is not a number (zero divided by zero). If true, returns the NaN expression. Otherwise returns the expression. | isnan([metric1], [metric2]) |
isnrn(expression, NrN expression) | Checks if expression is a real number, such as infinity, nan, or null. If true, returns NrN expression. Otherwise returns the expression . | isnan([metric1], [metric2]) |