Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

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])
  • Was this article helpful?