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.

Practice

1. Which logical function in Tableau is best for checking a simple true or false condition?

easy

A. IF

B. CASE

C. IIF

D. SWITCH

Solution

1. Step 1: Understand the purpose of IF

   The IF function evaluates a condition and returns one value if true and another if false, perfect for simple true/false checks.

2. Step 2: Compare with other functions

   CASE is for multiple fixed values, IIF is a shorthand for IF but with null handling, and SWITCH is not a Tableau function.

3. Final Answer:

   IF -> Option A

4. Quick Check:

   Simple true/false check = IF [OK]

Hint: Use IF for simple true/false decisions [OK]

Common Mistakes:

• Confusing CASE with IF for simple conditions
• Using IIF without needing null handling
• Thinking SWITCH is a Tableau function

2. Which of the following is the correct syntax for the IIF function in Tableau?

easy

A. IIF(condition ? true_result : false_result)

B. IIF(condition THEN true_result ELSE false_result END)

C. IIF(condition, true_result, false_result)

D. IIF(condition, true_result)

Solution

1. Step 1: Recall IIF syntax

   IIF takes three arguments: a condition, the result if true, and the result if false, written as IIF(condition, true_result, false_result).

2. Step 2: Check other options

   IIF(condition THEN true_result ELSE false_result END) uses IF syntax, C uses ternary operator syntax (not Tableau), and D misses the false_result argument.

3. Final Answer:

   IIF(condition, true_result, false_result) -> Option C

4. Quick Check:

   IIF syntax = IIF(condition, true, false) [OK]

Hint: IIF always needs three parts: condition, true, false [OK]

Common Mistakes:

• Omitting the false_result argument
• Using IF syntax inside IIF
• Confusing with ternary operator syntax

3. What will be the result of this Tableau calculation?

CASE [Region]
  WHEN 'East' THEN 'Group A'
  WHEN 'West' THEN 'Group B'
  ELSE 'Other'
END

If [Region] is 'West'?

medium

A. 'Group A'

B. 'Group B'

C. 'Other'

D. Null

Solution

1. Step 1: Understand CASE evaluation

   The CASE statement checks [Region] value. If it matches 'West', it returns 'Group B'.

2. Step 2: Apply the input value

   Since [Region] is 'West', the second WHEN condition matches, so output is 'Group B'.

3. Final Answer:

   'Group B' -> Option B

4. Quick Check:

   CASE 'West' = 'Group B' [OK]

Hint: CASE returns first matching WHEN value [OK]

Common Mistakes:

• Choosing ELSE value when a WHEN matches
• Confusing CASE with IF syntax
• Assuming Null if no match without ELSE

4. Identify the error in this Tableau formula:

IF [Sales] > 1000 THEN 'High' ELSEIF [Sales] > 500 THEN 'Medium' ELSE 'Low' END

medium

A. ELSEIF should be ELSE IF (two words)

B. Missing END keyword

C. Incorrect use of ELSE instead of ELSEIF

D. IF cannot compare numeric values

Solution

1. Step 1: Check IF syntax in Tableau

   Tableau requires ELSE IF as two separate words, not ELSEIF as one word.

2. Step 2: Verify other parts

   The formula has END keyword, ELSE is used correctly, and IF can compare numbers.

3. Final Answer:

   ELSEIF should be ELSE IF (two words) -> Option A

4. Quick Check:

   Use ELSE IF, not ELSEIF in Tableau [OK]

Hint: Use ELSE IF as two words in Tableau IF statements [OK]

Common Mistakes:

• Writing ELSEIF as one word
• Forgetting END keyword
• Thinking IF can't compare numbers

5. You want to create a calculated field that labels sales as 'Low' if below 500, 'Medium' if between 500 and 1000, and 'High' if above 1000. Which Tableau formula correctly uses logical functions to do this?

hard

A. IF [Sales] <= 500 THEN 'Low' ELSEIF [Sales] < 1000 THEN 'Medium' ELSE 'High' END

B. CASE WHEN [Sales] < 500 THEN 'Low' WHEN [Sales] <= 1000 THEN 'Medium' ELSE 'High' END

C. IF [Sales] < 500 THEN 'Low' ELSEIF [Sales] <= 1000 THEN 'Medium' ELSE 'High' END

D. IIF([Sales] < 500, 'Low', IIF([Sales] <= 1000, 'Medium', 'High'))

Solution

1. Step 1: Analyze the requirement

   We need three labels based on sales ranges: below 500, 500 to 1000, and above 1000.

2. Step 2: Evaluate each option

   IF [Sales] < 500 THEN 'Low' ELSEIF [Sales] <= 1000 THEN 'Medium' ELSE 'High' END uses ELSEIF (incorrect syntax, should be ELSE IF). CASE WHEN [Sales] < 500 THEN 'Low' WHEN [Sales] <= 1000 THEN 'Medium' ELSE 'High' END uses CASE incorrectly because CASE in Tableau matches exact values, not ranges. IF [Sales] <= 500 THEN 'Low' ELSEIF [Sales] < 1000 THEN 'Medium' ELSE 'High' END has overlapping conditions and incorrect ELSE IF syntax. IIF([Sales] < 500, 'Low', IIF([Sales] <= 1000, 'Medium', 'High')) correctly nests IIF functions to handle ranges properly.

3. Final Answer:

   IIF([Sales] < 500, 'Low', IIF([Sales] <= 1000, 'Medium', 'High')) -> Option D

4. Quick Check:

   Nested IIF handles ranges correctly [OK]

Hint: Use nested IIF for multiple range conditions [OK]

Common Mistakes:

• Using CASE for range conditions
• Writing ELSEIF instead of ELSE IF
• Overlapping or missing conditions