6.2. The CASE Function in Looker Studio
Let's dive into a more advanced function for categorizing data: the CASE function.
The CASE Function
Imagine working with a client that only operates in certain US states. They don't expect any conversions from non-serviceable states. In this scenario, you'd want to categorize all states into either serviceable or non-serviceable. The CASE function is perfect for this.
Let's consider a similar example with countries. You might have an e-commerce store that only ships to certain countries. You can categorize your traffic into serviceable or non-serviceable countries using the CASE function.
The basic structure of the CASE function is as follows:
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END
The CASE function starts with the
CASE keyword and ends with the
END keyword. Between these keywords, you can have as many
THEN clauses as you want. Each
WHEN clause has a condition, which could be any expression that returns true or false. If the condition is true, the value after the corresponding
THEN clause will be returned.
Here's an example of using the CASE function:
CASE WHEN Country IN ('United States', 'Canada') THEN 'Serviceable' ELSE 'Non-serviceable' END
In this example, if the country is in the United States or Canada, the function returns "Serviceable." Otherwise, it returns "Non-serviceable."
Keep in mind that the CASE function can only be used with
WHEN, not with
IF example was only used to help illustrate how the function works.
The IN Operator
The CASE function example above introduced the
IN operator, which is useful when you want to check if a value is equal to any of the values listed within the parentheses. It returns true if the value of the dimension matches any of the values listed in the
Country IN ('United States', 'Canada')
This expression returns true if the country is either the United States or Canada.
Keep in mind that the IN operator in Looker Studio is case-sensitive.