Case statements are conditional expressions used in calculated fields. They define one or more conditions and returns a result when a condition is met. You can define a default result if none of the conditions is met. Case statements are most often used to create new categories or groupings of data. Before proceeding, you might want to check previous training on calculated fields here.
How to create CASE statements?
CASE statements are boolean values, and they return the value you specify.
A common use for CASE is to create new categories or groupings of data. For example, to group selected country values into a Sales Region dimension, you might create a CASE expression like this:
You can type in CASE arguments while creating new fields. So;
- From right setting pane, click on Add Metric
- From the opened menu, click on Create Field
- Type in the CASE statement. You will see a green check if arguments works.
These steps can be also done from Resource → Manage added data sources → click on desired data source screen.
How to construct a CASE Statement block
A CASE expression begins with the CASE keyword and ends with the END keyword. In between, you'll have a number sections or "clauses":
- WHEN: a condition you want to evaluate. You can have multiple WHEN clauses in a single CASE expression.
- THEN: the result to return if the WHEN clause's condition is true. You must have one THEN clause for each WHEN clause in your CASE expression.
ELSE. Optional. If none of the WHEN clause conditions are true, CASE returns the value in the ELSE clause, or
NULLif no ELSE clause is specified.
CASE evaluates each successive WHEN clause and returns the first result where the condition is true. Any remaining WHEN clauses and the ELSE result are not evaluated. If all WHEN conditions are false or NULL, CASE returns the ELSE result, or if no ELSE clause is present, returns NULL.
All of the possible results in a THEN clause must be of the same type. For example, if the first THEN clause returns Text, additional THEN clauses, as well as the ELSE clause must also return Text.