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

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

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. Let us start by exploring the IF statement.

 

1. IF Statement

1.1 Use Case and Syntax

The IF statement is used to evaluate values within a dimension (such as category = “laptop”), a measure (such as profit > 100) or even aggregated values (such as SUM(profit) > 100).

 

The syntax of an IF statement is:

IF condition

THEN do this when the condition is true

ELSEIF next condition

THEN do this when the next condition is true

ELSE do this when none of the above conditions is true

END

Both the ELSEIF and ELSE statements are optional and are only necessary when we need to evaluate multiple conditions and output multiple results. Do note that if ELSE is omitted, all values not captured in IF will return NULL.

 

1.2 Strengths

The IF statement is the most flexible type of conditional statement in Tableau. When combined with ELSEIF, they can evaluate multiple conditions. IF statements can even accept operators, allowing them to evaluate more complex conditions simultaneously. For example, consider this sample dataset:

 

• IF statement with a dimension

 

• IF statement with a measure

 

• IF statement with operators

 

1.3 Limitations

The IF statement is so flexible that it really doesn’t have any limitations. 

Note that in older versions of Tableau, it used to be the case that IF statements cannot return Booleans, but this limitation has already been addressed in newer versions. See this blog for an example of the original limitation.

 

1.4 Common Pitfalls

Although the IF statement appears simple enough, there are actually a number of caveats to it. This section will address these caveats so that you can avoid the common errors.

• The order of conditions matters: 

Upper conditions are evaluated first, and as soon as the statement evaluates to True, the condition ends and doesn’t test any further. We can see below that Software has been misclassified as “Medium Revenue” because the order of evaluation is wrong. To fix, [Revenue] > 30000 THEN “High Revenue” should be placed in the top line.

 

• Cannot mix aggregate and non-aggregate comparisons or results in “IF” expressions:

Conditions and returned outputs should either all be non-aggregate values or all be aggregate values, Tableau does not allow mixing them together. In the example below, Product is not aggregated while Expenses are, throwing an error.

 

To fix this problem we can move the aggregation (SUM) outside of the IF statement.

 

Another common case is when using a field that is already an aggregation. For example, when using a parameter allowing the user to choose their measure, some of the measures may already be a calculation that is aggregated. Notice that “Percent Total” is an aggregated calculation, while “Revenue” is not, therefore mixing them both in the IF statement causes an error.

 

To fix this problem, we can simply aggregate “Revenue” inside the IF statement to make all the fields in the conditional statement aggregations.

 

• Use ELSEIF rather than ELSE IF:

Although not technically an error, we should still avoid using ELSE IF and use ELSEIF whenever possible. This is because each nested IF statement is evaluated as a separate query while ELSEIF is computed as part of the same query as its parent IF statement. Therefore, when working with complex logic statements, ELSEIF will run faster than ELSE IF.

 

I hope you enjoyed the first part of this series on Conditional Statements. Stay tuned for the next part!