The power of a simple IF statement

One of the first things I learned about Tableau was IF statements. IF statements are one of the
most useful decision-making functions in Tableau, so I would often use them when analyzing
data. Understanding how they work is vital in getting the correct output. This blog can help you out if
you are a beginner wanting to use IF statements or if you are curious about how they work.

A typical IF statement in Tableau would look like this:

This would result in the next graph:

As you can see the results are categorized into three categories. In the example, the Superstore data reveals that under the high-profit category the Central region is attaining the most profit. Using an IF statement in a calculated field is common practice if you want to categorize your data.

There are actually three variants of an IF statement. There is an IF, IF-ELSE, and ELSEIF variant and the typical example combines the three components.

IF

If you have only one condition you want to meet you can use the IF variant. The IF lets Tableau know that an IF statement is about to be performed and THEN shows that we are stating some return value. When the condition is met (i.e. TRUE) it will return the results but if the condition is not met (i.e. FALSE) then it returns a NULL. This is also known as a conditional expression or Boolean expression since the results would either be TRUE or FALSE.

In the example above, the results are divided into two categories: high profit and null. Null refers to the profit that is not equal to or above 2000 and does not meet the first condition.
 

IF-ELSE

This variant also tests for one condition just like the IF variant. The difference is that the unmatched results will not show NULL but instead will show the chosen return value.

In the example above, results will not show NULL but rather the string value ‘Low Profit’.

ELSEIF

This variant is used when you want to check for multiple conditions. The ELSEIF condition can be exploited when the prior IF function is not met. The function performs in a sequential manner. Tableau checks if the first condition is met (i.e. TRUE) and if this condition is not met (i.e. FALSE) it will check the next ELSEIF condition. It will persist until there is an ELSEIF statement that satisfies the condition.

In the example above, all data that meets the ELSEIF condition, profit less than 2000 and above 1000, returns the string value ‘Medium Profit’.

Avoid redundant logic

It is common to see the ELSEIF variant in a typical IF statement to have an AND to signify the logic. However, note that the results of an IF statement with an ELSEIF variant within the same calculated field are mutually exclusive. So, the same data point cannot fall into more than one category.

So, in the example, if I write ELSEIF [Profit]>1000 THEN ‘Medium Profit’ the results would be exactly the same. The ‘<2000’ logic is redundant because profits that are >= 2000 are already handled in the first IF clause.

In general, I strongly discourage the use of redundant logic within your ELSEIF statements, because the chances of error increase when you do so. Why? Imagine the company is doing well, and the finance department wants to redefine what High Profit means. Instead of 2000, they want to categorize profits above 3000 as High Profit. In your haste to do so, you might forget that you need to update the logic in 2 places, so you only update the first part of your calculation.

The example above is obviously wrong because now profits between 2000 and 3000 are not captured as 'Medium Profit’ but now fall into the 'Low Profit' category.

END

In order to complete an IF statement, it must be concluded with an END.

Logical operators

If you want to use a logical operator (e.g. AND) you have to keep in mind how they work. Tableau supports NOT, AND, or OR logical operators in a Boolean statement.

In the example above, we are using an IF statement with two logical operators (e.g. AND and OR). Once you are combining two logical operators, the order of the operators becomes crucial in order to get the right result. Innately, you could think that this calculation results in getting art or binders products ordered after 2020, but this is not the case.

In this case, Tableau will first analyze the AND and will look at both sides of the AND operator. This is because of the order of operations for logical operators. The order of operations is AND, NOT, and OR. Parentheses are used to override priority.

AND will look at both sides of the operator. Thus, it will look at binders and products that were ordered after 2018. With AND both criteria will need to be met (i.e. True) so only binders that were ordered after 2018 will be included in the results.

Then it would look at the OR and for OR only one side needs to be met (i.e. TRUE). Meaning it will search for art and binders that were ordered after 2018. The results would also include art products that were ordered in 2012 since the only criteria are art.

If you wanted the results to show both art and binders that were ordered after 2018 you need to use parentheses. It is best practice to use parentheses to define the logic you want to use. Now that we added the parentheses to override the priority it is yielding the right results.

Data type

A final note is that you will get an error if you try to combine two data types. In this case, you are trying to combine a string and a whole number. This is wrong because an IF statement needs to be the same data type. So, choose one data type!

Hopefully, this blog gave you a push in the right direction to learn about IIF statements.

Also, a shout out to the Flerlage Twins, Emily Dowling, and Tableau on their own written works on IF statements since it also inspired me to write about IF statements.


- Saba

Empower your organization with intuitive analytics

Tableau is designed to put the user first because data analysis should be about asking questions and not about learning software. With built-in visual best practices, Tableau enables limitless visual data exploration without interrupting the flow of analysis.

As the market-leading choice for modern business intelligence, the Tableau platform is known for taking any kind of data from almost any system and turning it into actionable insights with speed and ease. It’s as simple as dragging and dropping.

We are a full-stack provider and integrator, relying on extensive experience and best practices to find your unique optimal set-up allowing you to tell the data stories you are eager to tell.