Operators and Functions for Conditional Formatting

Last updated
Save as PDF

Describes the operators and functions for creating conditional formatting expressions in dashboard charts.

Syntax Rules

Basic syntax: first-expression comparison-operator second-expression, such as ([Quick Ratio] - 10) * 2 >= 19

An expression can reference any data series in the chart, including data from an account, calculation, or custom metric. The data series name (not code) or the calculation name must be enclosed in brackets. For example, [1000 Assets]or [QuickRatio]. You can find the data series name from the Data Settings tab:

Data Series Name from Expanded Account Section in the Data Settings tab

Operator precedence. Evaluates left to right or enclose in parentheses to calculate first.

Comparison

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

Mathematical

Use mathematical operators with the expressions.

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`

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