Tableau Case Function – When Should You Use it?
CASE and Nested Case Functions in Tableau
Summary:
“CASE-WHEN” is the part of Logical functions in Tableau. “CASE-WHEN” are used to perform the logical test and return the required value when the test expression is true. Check out Tableau For Data Science Course for more tips and tricks or 40 Awesome Tableau Keyboard Shortcuts
How CASE-WHEN works:
It starts with “CASE” function evaluating the 1st logical expression corresponding to set/sequence of values and when the logical expression becomes True, it returns the respective specified value as result. If in logical expression no match is found then the value of default return expression is used. In case no default value is being mentioned by the user then “NULL” is being returned.
Syntax of CASE-WHEN:
This function finds the first <value> that matches the given <expression> and returns the corresponding <return value>
Example:
CASE [<expression>] WHEN <value1> THEN <return value1> WHEN <value2> THEN <return value2> ELSE <return value3> END
Advantages of using CASE-WHEN:
1. CASE-WHEN statements are easier to write and comprehend.
2. Because of its simplicity, for a user, it is helpful to avoid making mistakes like referencing the wrong field
3. CASE-WHEN statements perform faster than IF-ELSE statements
Disadvantages of using CASE-WHEN:
1. Usage of CASE-WHEN in Tableau is very limited as they cannot perform boolean algebra conditions
2. CASE-WHEN in tableau only compares the expression to the exact values. Conditional operators like “OR”, “AND” can’t be used with CASE-WHEN
3. Using CASE-WHEN, multiple expressions can’t be evaluated in a single line
Examples of CASE-WHEN:
Simple CASE-WHEN Statement:
Let’s say there is a field named as “Grade” in the dataset. The values in the “Grade” field are “A”, “B”, “C” and “D”. Now, suppose the requirement is to assign the weighted number(“5” for grade “A”, “4” for grade “B” etc.) based on the grades. Using CASE-WHEN statement this can be done easily in Tableau. Following is the syntax of this:
CASE [Grade] WHEN “A” THEN 5 WHEN “B” THEN 4 WHEN “C” THEN 3 WHEN “D” THEN 2 ELSE 1 END
In the above syntax, if there is no grade mentioned in that case default value 1 is get assigned.
Nested CASE-WHEN Statement:
Let’s consider the below dataset:
Region | Country | Sales |
Asia | India | |
Asia | China | |
Europe | France | |
Europe | Italy | |
Europe | Spain | |
USA | USA |
Now the requirement is to allocate the sales values based on the regions and countries. To do that directly in tableau, we can use nested CASE-WHEN statement as shown below:
CASE [Region] WHEN “Asia” THEN (CASE [Country] WHEN “India” THEN 2000 WHEN “China” THEN 2500 END) WHEN “Europe” THEN (CASE [Country] WHEN “France” THEN 3000 WHEN “Italy” THEN 2200 WHEN “Spain” THEN 1575 END) WHEN “USA” THEN (CASE [Country] WHEN “USA” THEN 3500 END) END
Here is the resulting Data
Region | Country | Sales |
Asia | India | 2000 |
Asia | China | 2500 |
Europe | France | 3000 |
Europe | Italy | 2200 |
Europe | Spain | 1575 |
USA | USA | 3500 |
How is CASE Different From IF or IFF
There are some fundamental differences between these functions. For the most part IF and CASE can be used interchangeably to match values and provide a return value. However, the difference here is that CASE statements cannot return a result based on a true-false statement otherwise known as a boolean. There IF is evaluating whether your condition is TRUE or FALSE while CASE is evaluating whether a value matches the value in the case expression. IIF statement is a simplified version of IF. It simply provides an answer when true, an answer when false, an answer when not false or true. This is equivalent to using an IF with ELSEIF and ELSE.
Examples:
If you wanted to return “Rich” for customers with greater than 1,000,000 in their savings account, CASE will not work because it only will match the value. Then is a true or false statement. Not a matching statement.
IF and IFF
IF [SAVINGS] > 0 THEN ‘Green’ ELSE ‘Red’ END
This will produce NULLs in your data because nothing happens when the value is 0
IF [SAVINGS] > 0 THEN ‘Green’ ELSEIF [SAVINGS]=0 THEN ‘Yellow’ ELSE ‘Red’ END
IFF [SAVINGS] >0 ,’Green’, ‘Red’, ‘Yellow’
IFF just gives you a quick and easy way to provide an answer for simple logical true or false. The third alternative is a wall to fill in NULLs.
A CASE function cannot work in the above example because there are logical true or false statements not when a condition is present.
When to use CASE, IF and IFF
- When simply matching values with values use CASE()
- For simple true and false(boolean) statements use IIF()
- For complex logic that requires calculations or multiple conditions use IF()