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

Operator	Description	Syntax
+	Adds two values.	`[metric1] + [metric2]`
-	Subtracts two values.	`[metric1] - [metric2]`
/	Divides two values.	`[metric1] / [metric2]`
*	Multiplies two values.	`[metric1] * [metric2]`
%	Calculates the remainder (modulus) when dividing two values.	`[metric1] % [metric2]`
^	Raises the value on the left to the power of the value on the right. For example, 5^ 2 = 25.	`[metric1] ^2`

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])

Operator	Description	Syntax
=	Checks if two values are equal.	`[metric1] = [metric2]`
<>	Checks if two values are not equal.	`[metric1] <> [metric2]`
<	Checks if the left value is less than the right value.	`[metric1] < [metric2]`
<=	Checks if the left value is less than or equal to the right value.	`[metric1] <= [metric2]`
>	Checks if the left value is greater than the right value.	`[metric1] > [metric2]`
>=	Checks if the left value is greater than or equal to the right value.	`[metric1] >= [metric2]`
BETWEEN	Checks if a value is between two other values. Minimum and maximum values are included in the range. Use with the AND operator.	`[metric1] BETWEEN 0 AND [metric2]`
NOT BETWEEN	Checks if a value is not between the two other values. Minimum and maximum values are included in the range. Use with the AND operator.	`[metric1] NOT BETWEEN 0 AND [metric2]`
AND	Compares two expressions and returns true if both expressions are true.	`([metric1] >= [metric2]) AND ([metric1] >= [metric3])`
OR	Compares two expressions and returns true if either expression is true.	`([metric1] >= [metric2]) OR ([metric1] >= [metric3])`

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

Function	Description	Example
min(expression, expression)	Returns the smallest value of two expressions.	`min([metric1], [metric2])`
max(expression, expression)	Returns the largest value of two expressions.	`max([metric1], [metric2])`
sin(expression)	Returns the sine of a metric or expression. Used in Trigonometry.	`sin([metric1])`
cos(expression)	Returns the cosine of a metric or expression. Used in Trigonometry.	`cos([metric1])`
tan(expression)	Returns the tangent of a metric or expression. Used in Trigonometry.	`tan([metric1])`
abs(expression)	Returns the absolute value of a metric or expression. Use to convert negative numbers to positive numbers.	`abs([metric1])`
sqrt(expression)	Returns the square root of a metric or expression.	`sqrt([metric1])`
round(expression)	Rounds a numeric value to the nearest whole number. The mid-point is rounded up. Examples: round(10.2) rounds down to 10 round(10.5) rounds up to 11 round(10.7) rounds up to 11	`round([metric1])`
round(expression, digits)	Rounds a numeric value to a specified number of digits. Positive digits round to the specified number of decimal places. Negative digits round to the nearest multiple of 10. Examples: round(2.15, 1) rounds up to 2.2 round(70, -2) rounds up to 100	`round([metric1], 1)`
floor(expression)	Returns a numeric value rounded down towards zero. Examples: floor(10.2) rounds down to 10 floor(10.5) rounds down to 10 floor(10.7) rounds down to 10	`floor([metric1], 2)`
floor(expression, expression)	Returns a numeric value rounded down towards zero, to the nearest multiple of significance. Examples: floor(3.5, 2) rounds down to 2	`floor([metric1], 2)`
ceiling(expression)	Returns number rounded up away from zero. Examples: ceiling(10) rounds up to 11 ceiling(10.5) rounds up to 11 ceiling(10.7) rounds up to 11	`ceiling([metric1])`
ceiling(expression, expression)	Returns number rounded up, away from zero, to the nearest multiple of significance. Examples: ceiling(10, 1) rounds up to 11 ceiling(10.5, 2) rounds up to 13 ceiling(10.7, 2) rounds up to 13	`ceiling([metric1], 2)`

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])`