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 WHEN
and 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
. The 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 IN
operator.
For example:
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.
β 6.3. Grouping 404 Pages with CASE Function
β 6.4. CASE Functions with Multiple Clauses
β 6.5. Tips for Editing Formulas in Looker Studio
β 6.6. Using CASE Function for Cleaning Data
β 6.7. Order of WHEN Clauses in CASE Function
β 6.8. Returning Expressions in CASE Functions
β 6.9. Using CASE to Extract URL Query Parameters
β 6.10. Mixing Dimension & Metrics in CASE
π© Receive my weekly Looker Studio tips
π₯Β Subscribe to my YouTube channel
π Connect with me on LinkedIn