Logical functions (IF, IIF, CASE) in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report that categorizes each sale based on the amount. The categories are: 'Low' for sales under $100, 'Medium' for sales between $100 and $500, and 'High' for sales above $500. Additionally, the manager wants to see a flag for sales made on weekends.

📊 Data: You have sales data including Sale ID, Sale Date, Customer, and Sale Amount.

🎯 Deliverable: Create a Tableau report with calculated fields using IF, IIF, and CASE to categorize sales and flag weekend sales. Then build a summary table showing counts of each category and weekend flags.

Sample Data

Sale ID	Sale Date	Customer	Sale Amount
1	2024-06-01	Alice	75
2	2024-06-02	Bob	150
3	2024-06-03	Charlie	600
4	2024-06-04	Diana	450
5	2024-06-05	Eva	90
6	2024-06-08	Frank	520
7	2024-06-09	Grace	300
8	2024-06-10	Hank	80
9	2024-06-11	Ivy	200
10	2024-06-15	Jack	700

1

Step 1: Create a calculated field named 'Sale Category' using IF function to categorize sales.

IF [Sale Amount] < 100 THEN 'Low' ELSEIF [Sale Amount] <= 500 THEN 'Medium' ELSE 'High' END

Expected Result

Sales with amounts 75, 90, 80 are 'Low'; 150, 450, 300, 200 are 'Medium'; 600, 520, 700 are 'High'.

2

Step 2: Create a calculated field named 'Weekend Flag' using IIF function to flag weekend sales.

IIF(DATENAME('weekday', [Sale Date]) = 'Saturday' OR DATENAME('weekday', [Sale Date]) = 'Sunday', 'Weekend', 'Weekday')

Expected Result

Sales on 2024-06-01 (Saturday), 2024-06-02 (Sunday), 2024-06-08 (Saturday), 2024-06-09 (Sunday), 2024-06-15 (Saturday) are flagged 'Weekend'; others 'Weekday'.

3

Step 3: Create a calculated field named 'Category Description' using CASE function to provide descriptions.

CASE [Sale Category] WHEN 'Low' THEN 'Small sale' WHEN 'Medium' THEN 'Moderate sale' WHEN 'High' THEN 'Large sale' ELSE 'Unknown' END

Expected Result

'Low' category sales show 'Small sale', 'Medium' show 'Moderate sale', 'High' show 'Large sale'.

4

Step 4: Build a summary table with rows as 'Sale Category' and columns as 'Weekend Flag'. Show count of sales in each cell.

Rows: [Sale Category], Columns: [Weekend Flag], Values: COUNT([Sale ID])

Expected Result

Table shows counts like: Low-Weekend=1, Low-Weekday=2, Medium-Weekend=2, Medium-Weekday=2, High-Weekend=2, High-Weekday=1.

Final Result

Sale Category Summary Table

+------------+---------+---------+
| Category   | Weekday | Weekend |
+------------+---------+---------+
| Low        |       2 |       1 |
| Medium     |       2 |       2 |
| High       |       1 |       2 |
+------------+---------+---------+

✓Most 'Low' sales happen on weekdays.

✓'Medium' sales are fairly balanced between weekdays and weekends.

✓'High' sales occur more on weekends than weekdays.

✓Weekend sales are significant across all categories.

Bonus Challenge

Create a calculated field that combines 'Sale Category' and 'Weekend Flag' into a single label, e.g., 'High-Weekend'. Then build a bar chart showing total sales amount for each combined label.

Show Hint

Use the + operator or CONCAT function in Tableau to join the two fields with a dash.