Operators and Functions for Calculations

Describes the operators and functions used in calculations for charts in the Discovery new user interface.

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