5 min read
This blog series aims to provide a detailed explanation of conditional statements in Tableau. We will cover the most widely used statements including IF, IIF and CASE. In particular, we will focus on establishing their correct syntax, the strengths, the limitations, and common pitfalls in detail.
- IF Statements (previous blog)
- IIF Statements
- IF vs. IIF
- CASE Statements
Logical conditions and conditional statements are essential components in a Data Scientist’s toolkit. Conditional statements help determine whether a condition or group of conditions is satisfied or not before making the business decision. Tableau provides a set of conditional statements that evaluates one or more conditions and outputs corresponding values depending on whether the condition holds TRUE or is FALSE. In this blog, we will continue with our journey and start with the IIF statements.
1. IIF Statements
1.1 Use Case and Syntax
The IIF statement stands for Immediate IF (also known as Inline IF) statement. It is a shorter way to write a simple IF statement that evaluates a single condition (IF and ELSE, but not ELSEIF). For more complex logical statements that involve nesting or ELSEIF, we should use the standard IF statement instead.
The syntax of an IIF statement is:
action when condition is true, action when false,
[action when unknown/neither true or false])
Similar to a standard IF statement, IIFs can also work with dimensions, measures and aggregated values.
- The IIF statement is shorter and faster to write compared with the standard IF.
- The IIF statement can handle unknown/Null values implicitly (more on this in the next section).
- The IIF statement is not as easy to read or interpretable as IF.
- The IIF statement cannot handle complex logic with multiple conditions.
1.4 Common Pitfalls
- The IIF statement shares similar pitfalls as the IF statement.
2. IF vs. IIF
As we have discussed, IF and IIF share many similarities, but they do have a key difference and that is how unknown/Null values are handled. Consider this sample dataset:
And compare the following IF and IIF statements
IF [Revenue] > 30000
IIF ([Revenue] > 30000, “High”, “Low”)
- IF – Explicit
ELSEIF [Revenue] > 30000
We can see that the standard IF statement without a clause that considers Null cases, simply categorizes the Nulls as whatever is in the ELSE clause. This can be misleading, because in this case, a missing revenue may indicate problematic data rather than low revenue.
The IIF statement by default handles Null values as “Null”. We can even explicitly state what we want the Nulls to appear as. For example, IIF ([Revenue] > 30000, “High”, “Low”, “Missing”).
In the IF statement, we can explicitly add an additional clause that handles Null values that replicate IIF’s behaviour.
The key takeaway here is that we need to be careful when using the IF statement in the presence of Null values.
3. Case Statements
3.1 Use Case and Syntax
The CASE statement tests for a specific and exact value, in other words, it only considers if a condition equals something. The CASE statement does not support more complex logic such as greater or less than, AND / OR clauses.
The CASE statement is most often used to replace the IF statement that has too many equal clauses for a single field (variable), for example categorizing items. A well-written CASE statement is easier to interpret than a long IF statement. The CASE statement can work with both dimensions and measures.
The syntax of a CASE statement is:
WHEN value_1 THEN value_a
WHEN value_2 THEN value_b
The ELSE clause isn’t compulsory, but without it, anything that is not captured by the other WHEN…THEN… clauses will return Null.
- When there are many “equal” logical statements, the CASE statement will often be more readable and faster to write than an IF statement.
- CASE statements are not as flexible as IF, and cannot evaluate AND/OR, >/< . But CASE statements can be combined with other logical functions such as IN or ISNULL.