Conditional Expressions

You use conditional expressions to create expressions that convert values.

The conditional expressions described in this section are building blocks for creating expressions that convert a value from one form to another.

Follow these rules:

• In CASE statements, AND has precedence over OR.
• Strings must be in single quotes.

Expression	Example	Description	Syntax
CASE (If)	CASE WHEN score-par < 0 THEN 'Under Par' WHEN score-par = 0 THEN 'Par' WHEN score-par = 1 THEN 'Bogey' WHEN score-par = 2 THEN 'Double Bogey' ELSE 'Triple Bogey or Worse' END	Evaluates each WHEN condition and if satisfied, assigns the value in the corresponding THEN expression. If none of the WHEN conditions are satisfied, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL. Note: See Best Practices for using CASE statements in Analyses and Visualizations.	CASE WHEN request_condition1 THEN expr1 ELSE expr2 END
CASE (Switch)	CASE Score-par WHEN -5 THEN 'Birdie on Par 6' WHEN -4 THEN 'Must be Tiger' WHEN -3 THEN 'Three under par' WHEN -2 THEN 'Two under par' WHEN -1 THEN 'Birdie' WHEN 0 THEN 'Par' WHEN 1 THEN 'Bogey' WHEN 2 THEN 'Double Bogey' ELSE 'Triple Bogey or Worse' END	Also referred to as CASE (Lookup). The value of the first expression is examined, then the WHEN expressions. If the first expression matches any WHEN expression, it assigns the value in the corresponding THEN expression. If none of the WHEN expressions match, it assigns the default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL. If the first expression matches an expression in multiple WHEN clauses, only the expression following the first match is assigned. Note See Best Practices for using CASE statements in Analyses and Visualizations.	CASE expr1 WHEN expr2 THEN expr3 ELSE expr4 END
IfCase > ELSE	-	-	ELSE [expr]
IfCase > IFNULL	-	-	IFNULL([expr], [value])
IfCase > NULLIF	-	-	NULLIF([expr], [expr])
IfCase > WHEN	-	-	WHEN [Condition] THEN [expr]
IfCase > CASE	-	-	CASE WHEN [Condition] THEN [expr] END
SwitchCase > ELSE	-	-	ELSE [expr]
SwitchCase >IFNULL	-	-	IFNULL([expr], [value])
SwitchCase > NULLIF	-	-	NULLIF([expr], [expr])
SwitchCase > WHEN	-	-	WHEN [Condition] THEN [expr]

Best Practices For Using CASE statements in Analyses and Visualizations

When using CASE statements in reports and workbooks, consider the report columns and the order of aggregation because these affect how expressions and sub-totals are calculated.

• In general, when using CASE expressions, make sure that any columns used in the expression are included in the report.

• If the order of aggregation is important, then change the report aggregation rule from Default to Sum.

• If a condition has to be evaluated before the aggregation is computed (for example, if the column filtered isn't displayed in the report), then use the FILTER function.

Example

This example use data for Brand, Product Type, and Revenue.

Description of the illustration case-best-practice-data.png

To apply a condition to set Camera revenue to 0, create the following conditional expression: CASE WHEN Product_Type = ‘Camera’ THEN 0 ELSE Revenue END.

When Product_Type is excluded from the report, data isn't available to evaluate the expression because it contains Product_Type, and the results are:

Description of the illustration case-best-practice-results-1.png

By adding PRODUCT_TYPE to the report and setting report aggregation to SUM, the result aggregates after the base level values are calculated:

Description of the illustration case-best-practice-results-2.png

An alternative to using CASE is to use a filter expression: FILTER(Revenue using Product_Type != ‘Camera’). The expression is calculated independently of report columns, and aggregations are applied after calculation:

Description of the illustration case-best-practice-results-3.png