Hi folks! Today we will talk about one of the most powerful and probably advanced functions in Looker Studio, the CASE() function.
I know this function might scare you and looks like programming and mathematics, but I assure you it’s much simpler than it seems.
In this article we will explore the CASE function, and how we can utilize it inside of our Looker Studio report.
In this article
WHAT IS THE CASE STATEMENT IN LOOKER STUDIO?
CASE function is one of the most advanced features of Looker Studio and it is used to evaluate one or more conditions and return an outcome when conditions are met.
With this function you have the possibility to build new dimensions and metrics, and create new categories or groupings of data.
To learn more about creating formulas in Looker Studio, see How To Create Calculated Fields in Looker Studio.
THE CASE FUNCTION SYNTAX
One of the most common uses of the CASE statement is to search for an element within a string and create new categories. Below you can see the basic syntax to follow:
CASE
WHEN condition THEN result
[WHEN condition THEN result]
[…]
[ELSE else_result]
END
For instance, to aggregate placements that have the same size and create a new field, you might use a CASE expression like this:
Case
To start us off, a CASE expression begins with the CASE keyword that evaluates what we are about to write next, and ends with the END keyword. In the middle, the CASE statement works by creating conditions using the following handlers: When, Then, Else, End.
When
The WHEN clause introduces a condition that needs to be evaluated. It analyzes all the data and returns TRUE if a specific condition is met, FALSE otherwise. What makes the CASE function powerful is also the great possibility to use any of the Looker Studio’s functions to identify the WHEN criteria. Last but not least, there can be multiple WHEN clauses in a single CASE statement.
Then
THEN clause indicates the output to return if the WHEN clause’s condition is satisfied. In order to work correctly, it is very important that you insert the THEN clause for each WHEN clause in your CASE expression. WHEN. In addition, it can retrieve the following values:
- Dimensions
- Metrics
- Literal values
- Calculated fields
- NULL
Else
If none of the WHEN conditions are met, you can optionally include the ELSE clause to specify a default result. If the ELSE clause is not included, and none of the WHEN clauses are satisfied, the statement returns NULL.
If you are already wondering how many ELSE clause’s you can use, I confirm that A CASE expression can only have one ELSE clause.
CONCLUSIONS
As you can see, the CASE statement is one of the most powerful statements in Looker Studio. There are nearly unlimited ways to use it in conjunction with all the other Looker Studio’s functions and leverage them to your advantage.
So what are you waiting for! Start testing this beautiful function and let me know what you think.
