Writing Better Conditional Statements to Avoid Common Pitfalls in Tableau – Part II

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.

 

Contents

Part I

• IF Statements (previous blog)

Part II

• 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:

IIF(condition,

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.

 

1.2 Strengths

• 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).

 

1.3 Limitations

• 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

IF [Revenue] > 30000

THEN “High”

ELSE “Low”

END

• IIF

IIF ([Revenue] > 30000, “High”, “Low”)

• IF – Explicit

IF ISNULL([Revenue])
THEN NULL
ELSEIF [Revenue] > 30000
THEN “High”
ELSE “Low”
END

 

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:

CASE condition

WHEN value_1 THEN value_a

WHEN value_2 THEN value_b

ELSE value_c

END

The ELSE clause isn’t compulsory, but without it, anything that is not captured by the other WHEN…THEN… clauses will return Null. 

 

3.2 Strengths

• When there are many “equal” logical statements, the CASE statement will often be more readable and faster to write than an IF statement.

 

3.3 Limitations

• 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.