Day 4 — IF, CASE and conditional logic
Making a calculation decide for itself - the basis of banding, flagging and grouping.
IF ... THEN ... END
The END is not optional. Forgetting it is the single most common syntax error in Tableau.
Every calculation on this page runs against the Orders data source below - 48 rows, dimensions in blue, measures in green, exactly as Tableau colours them.
Order matters
[Quantity] >= 5 before [Quantity] >= 15, every bulk order is labelled Medium and the first test never gets a chance. Always go from the most extreme condition to the least.Counting and totalling with IF
The two sales figures add up to the total, which is the check worth running whenever you split a population in two. SUM(IF ... THEN 1 ELSE 0 END) is the standard way to count rows meeting a condition.
AND, OR, NOT
The last two agree, because every row is either Technology or Accessories.
CASE - cleaner when you are matching one field
Grouping into bands, then using the band
West does far more of its business in bulk orders than the other regions - the kind of finding a report exists to surface.
Nulls in conditions
No order has 100 units. With ELSE 0 you get 0; without it every row returns Null, SUM of nothing is Null, and the view shows an empty cell. ZN() turns a Null into a zero when a blank would look like a mistake.
Try these yourself
- Band Sales into "High" above 100000, "Medium" above 20000, otherwise "Low".
- Count how many orders are Express ship mode.
- Total the sales for Technology in the East region.
- Rewrite the Zone calculation using IF instead of CASE.
- Explain the difference between leaving ELSE off and writing ELSE 0.