Using Conditional Statements

Conditional statements are formulas that let you create additional categories in your data set. When the condition is met, the new category is added to the widget without modifying the data set itself.
For example, if you want to calculate only certain values according to conditions you define, you can use conditional statements to include only the values of interest. There are several conditional statements that you can use to evaluate criteria and display only the relevant results in your widgets.
Note: Conditional statements are supported only for measures and aggregations and return numeric values.
IF
IF(Boolean_expression, <numeric expression 1>, <numeric expression 2>)

The IF function evaluates a boolean expression. When the expression is true, numeric expression 1 is returned. When the expression is false, numeric expression 2 is returned.

Example:

IF(SUM([Total Printed Sheets]) < 10, 0, SUM([Total Printed Sheets]) )

Nested IF statements are also supported.

Example:

IF(SUM([Total Printed Impressions]) < 100, 1, (IF(SUM([Total Printed Impressions]) < 1000, 2, 3) ) )

isNull
ISNULL(<numeric expression>)

The isNull function returns true if the expression does not contain data, or is Null. It can be used as a condition inside conditional statements.

Example:

IF(ISNULL(SUM([Total Printed Impressions])), 0, SUM([Total Printed Impressions]) )

CASE
CASE
WHEN Boolean_expression THEN <result_expression>
[ ... ]
[ ELSE <else_result_expression> ]
END

The CASE function evaluates boolean expressions. When the expression is true, the corresponding result_expression is returned. If no match is found, the else_result_expression is returned. If there is no default returned and no values match, Null is returned.

Example:

CASE
WHEN COUNT([Job ID]) < 100 THEN 1
WHEN COUNT([Job ID]) < 1000 THEN 2
ELSE 3
END

Parent topic: Using Formulas