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

Operators and Functions for Calculations

Contains preview content for the upcoming 2018.1 release.

Syntax Rules

  • Accounts and calculations and custom metrics. Required. Use the account code (not name) or use the calculation code in expressions. For example, 1000_Assets or 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(Net_Income <= (laptopRev + destopRev + tabletRev), Net_Income, 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. Used 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. Used 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?