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. 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 enclosed 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. 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` value.	isnull([metric1], [metric2])
isinfinity(`expression`, `infinity expression`)	Checks if `expression` is infinity (divided by zero). If true, returns the `infinity expression` value. 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` value. Otherwise returns the `expression` value.	isnan([metric1], [metric2])
isnrn(`expression`, `NrN expression`)	Checks if `expression` is a real number, such as infinity, nan, or null. If true, returnsthe `NrN expression` value. Otherwise returns the `expression` value.	isnan([metric1], [metric2])