What is Tableau CASE Statement?
Conditional statements are available in every programming language out there. When you want to categorize large amounts of raw data (non-numeric) and need the exact values, the CASE statement is used to sort it out. With a working similar to Excel’s Power Query and vast computing powers, the CASE function is one of the most essential conditional statements that Tableau can offer.
Suppose you are analyzing the Iris dataset, and you want to categorize the species more efficiently; you can use the Tableau Case When statement and end the CASE statement with the “END” as seen in the example below.
Once you apply these examples, you can place both the species and the calculated field in the same row to see the result.
Table of contents
Key Takeaways
- The CASE statement in Tableau allows you to implement conditional logic within calculated fields.
- It supports evaluating multiple conditions and returning different results based on each condition.
- Utilizes the CASE keyword followed by WHEN and THEN clauses to define conditions and corresponding results.
- Optionally includes an ELSE clause to specify a default result if none of the conditions are met.
- Requires an END keyword to close the CASE statement.
- Offers flexibility in handling various scenarios, including categorization, filtering, and data transformation.
Syntax
The syntax of using the Tableau nested CASE statement is:
CASE [expression]
WHEN condition1 THEN result1
WHEN condition2 THEN result2
…
ELSE default_result
END
Where,
expression could mean the column name to perform the Tableau CASE WHEN statement.
How to use Tableau CASE statement?
Follow the steps below to see how to use the Tableau Nested Case statement.
Step 1: Open Tableau and select a “New” workbook to perform your operations.
Then, select the dataset that you want to work on. Here, the Sample Superstore dataset is used.
Step 2: Select the “Region” feature and place it on the Rows.
Step 3: In the “Data” tab, select the dropdown and click “Create Calculated Field.”
It will open a popup where you can start forming your conditional statements.
Step 4: Rename the name of the field and start your Tableau CASE statement.
Step 5: Drag and drop the feature in the calculated field. It makes it so that you don’t need to manually type all the features, reducing spelling errors or misclassification.
Here, the Regions field is set as the case to be evaluated.
Step 6: Write the case statements with the Region values as shown.
Step 7: Continue the other statements. Once you are done, end the CASE Statement.
Step 8: Select the “State” and “Country” features and set them in the marks along with the new field. Convert them into the map graph to visualize it.
Step 9: To make the areas look distinct, drag and drop the calculated field in the color column in the Mark tab.
This sets the color change to be reflected on the CASE statement used in the calculated field. Now, this is the final graph.
Code:
case [Region]
when “South” then “Area1”
when “East” then “Area1”
when “West” then “Area2”
when “Central” then “Area2”
end
Examples
Let us see the different ways to use the Tableau CASE statement multiple fields function below.
Example 1: Tableau ‘CASE’ with multiple conditions
Suppose you have a large dataset, and you want to declare multiple conditions in a single statement; you can use the CASE statement in Tableau. Here’s how you can use Tableau to categorize the ‘Ship Order’ feature in the Sample Superstore dataset.
Step 1: In a new workbook, connect the dataset.
Step 2: Drag and drop the “Ship Mode” feature in the “Rows” area. You need not add any other values (they are irrelevant).
Step 3: Create a new Calculated field in the “Data” tab.
Step 4: Name the calculated field.
Step 5: Start a CASE statement and drag the feature you want to create conditional statements with.
Step 6: Using the Tableau CASE WHEN statement, you can define the parameters as shown.
Step 7: Similarly, define the other values in the feature and set cases for them, as shown.
End the CASE statement with “END.” Not doing so will cause syntax errors.
Step 8: Click Apply, and then drag the new field to the rows section to see the changes.
It has worked successfully.
Code:
CASE [Ship Mode]
WHEN “First Class” THEN “1”
WHEN “Same Day” THEN “2”
WHEN “Second Class” THEN “3”
ELSE “4”
END
Example 2: Tableau ‘CASE’ to compare numeric field
Even though you cannot use Tableau’s CASE statement to compare numbers in a range, you can use the CASE statement to compare the exact values of those numbers and provide a description (not doing so will have unintended consequences). The numerical values will be statistically altered by default.
Step 1: Add the “Sample Superstore” dataset in a new workbook.
Step 2: Drag and drop the “Ship Mode” and the distinct sales count of each delivery type.
Every numerical value in Tableau is aggregated using a statistic value by default. It may be sum, average, median, Count, Distinct count and so on. To select the specific measure, simply right-click on the numerical field and go over to “Measures” in the drop-down. Then select the measure of your choice. Here, it is the Distinct Count.
Step 3: Click on the dropdown in the Data tab and select “Create Calculated Field.”
Step 4: Name the calculated field.
Step 5: Start with the CASE function of the sales feature. In this case, you can change the sales to the Distinct Count.
Step 6: Since the profit is an integer, there is no need to use an integer as shown.
Step 7: Similarly, copy another value from the table and provide a description using the Tableau CASE When statement.
Step 8: End the CASE function and then Apply it.
Step 9: Set the calculated field on the rows column and view the results.
Code:
CASE COUNTD([Sales])
WHEN 1658 THEN “Profit”
WHEN 4254 THEN “Excellent Sales”
ELSE “Medium Sales”
END
Example 3: Tableau ‘CASE’ with ‘IF’ statement
While you cannot use the CASE statement with the IF statement, you can mimic the Tableau CASE statement with the IF statement. Here, you can declare ranges since the IF statement is compatible with numerical ranges. Follow the steps below.
Step 1: Drag and drop or select the dataset for the new workbook.
Step 2: Drag and drop the “Ship Mode” and the total sum of sales made by each delivery type.
If you drag and drop the Sales column, Tableau will find the “default” measure that is, the SUM of the numerical feature-Sales. Drag and drop the Sales field in the “text” part of the “Marks” tab.
Step 3: In the Data tab, click the dropdown and select “Create Calculated Field”.
Step 4: In the popup, name the calculated field.
Step 5: Declare an IF statement declaring the range between 100,000 and 300,000 and set the Sales description.
Step 6: Set an ELSEIF statement for the sales range between 300,000 and 500,000.
Step 7: Set an ELSE statement for numbers greater than 500,000.
Then, end the IF conditional statement.
Step 8: Apply the calculated field, close the popup and drag the calculated field to the rows component as shown.
Code:
IF SUM([Sales])>=100000 AND SUM([Sales])<=300000 THEN “Decent” ELSEIF SUM([Sales])>=300000 AND SUM([Sales])<=500000 THEN “Good”
ELSE “Excellent Profit”
END
Example 4: Tableau CASE with dates Field
When you have a time-series dataset with dates for a sales dataset, you can make things easier by defining the Quartiles and months in a different column if you need them for your calculation and need them as a separate column. It can be done using the Tableau CASE statement.
Step 1: Select the “Sample Superstore” dataset for this example (you can use any time-series dataset with date datatypes in the data).
Step 2: Select the order date on the row component and select “Month” by right-clicking on the feature.
Step 3: Create a new calculated field.
Step 4: Before starting with the CASE statement, name the calculated field.
Step 5: Define the CASE conditional statement for the MONTH of the Order Date.
If you drag and drop the feature, the expression will automatically be written as seen above.
Step 6: Using the nested CASE statement with the IN function, define the quartile number based on the number of months.
If it is the first 3 months, it is the first quartile or “Q1”.
Step 7: Similarly, write down all the other quartiles and end the CASE Statement in Tableau.
Step 8: Apply and close the Calculated Field. It will be available in the features of the data. Then, drag and drop the calculated field into the row component.
Code:
CASE DATEPART(‘month’, [Order Date])
WHEN IN(1,2,3) THEN “Q1”
WHEN IN(4,5,6) THEN “Q2”
WHEN IN(7,8,9) THEN “Q3”
WHEN IN(10,11,12) THEN “Q4”
END
Important Things To Note
- Use CASE statements for implementing conditional logic within calculated fields.
- Clearly define each condition and its corresponding result to ensure clarity and accuracy.
- Avoid overly complex CASE statements that are difficult to understand or maintain.
- Don’t forget to include an ELSE clause with a default result to handle cases not covered by the specified conditions.
- Avoid nesting multiple CASE statements within each other as it can reduce readability.
- Don’t use CASE statements unnecessarily for simple conditional logic that can be achieved with simpler functions like IF.
- The CASE statement is incompatible with numerical ranges. Hence, you cannot perform the Tableau case statement greater than numbers. Instead, use the IF conditional statement.
Frequently Asked Questions (FAQs)
• CASE statements support multiple conditions and results, while IF-THEN-ELSE statements handle only one condition.
• CASE statements offer more concise syntax for handling complex logic.
• CASE statements can handle null values more conveniently than IF-THEN-ELSE statements.
• IF-THEN-ELSE statements may be more readable for simple conditions and logic.
• Define a parameter in Tableau.
• With that, you’ve created a new parameter (look at the bottom left of the screen).
• Define a calculated field.
• Name the calculated field and then define it using the CASE statement in Tableau.
• The field will be saved in your table feature’s list.
Recommended Articles
This has been a guide to Tableau Case Statement. Here we explain how to use case statement in Tableau with examples & points to remember. You can learn more from the following articles –
Leave a Reply