IF Statement Tableau: Mastering Logical Functions

IF statements are an essential component of Tableau’s powerful calculation language. They allow you to control logic flows and make decisions based on specific criteria and conditions. With IF/THEN/ELSE logic, you can fine-tune complex calculated fields, table calculations, and parameters to adapt dynamically based on your underlying data.

In this comprehensive guide, we will explore the fundamentals of designing and implementing robust IF statement Tableau. You will learn about comparison operators, nesting IF statements, IIF function, and CASE statement usage in different Tableau contexts, with examples. Read on!

What is the IF Statement Tableau?

IF Statements are a fundamental part of not just Tableau, but of other Analytics Platforms and Programming Languages as well. Logical calculations help in evaluating particular conditions against given values to facilitate correct and effective decision-making. There are 3 variants of the IF Statement Tableau: IF, IF-ELSE, and ELSEIF. Let’s start with the IF variant first.

The IF Statement Tableau returns the result (i.e. TRUE) only if the given condition is met, but if the condition is not met (i.e. FALSE) then it returns a NULL value. This is also referred to as conditional expression or Boolean expression as the result would be in the form of TRUE or FALSE.

Syntax

A typical IF Statement Tableau looks like this:

IF <Expression> THEN <True_Statement>
END

Let’s break this down and try to understand its various components.

• The IF keyword tells Tableau that an IF Statement is being performed.
• <Expression> represents the conditional expression which is nothing but a boolean statement—an evaluation that returns either TRUE or FALSE.
• The THEN keyword indicates that some return value is being specified.
• <True_Statement> is the value that will be returned if the conditional expression is TRUE.
• END represents the end of the loop.

IF Statement Tableau Example

To execute the IF Statement Tableau, you would need to create a Calculated Field. You can do so by clicking on the “Analysis” tab after loading your dataset in Tableau. Select “Create Calculated Field…” and enter the code for the calculation that you want to perform.

Here, you need to enter the code for IF Statement Tableau as shown.

Now, add this Calculated Field to the table to apply it to your dataset in Tableau. Now, you can observe in this example that the results are divided into 2 categories: Performing Good and Null. Null specifies that the condition is failed and represents the profit that is less than 0.

There are some exceptions in IF statement Tableau that need to be taken care of while performing any queries.

First of all, an IF statement only operates on a single row. For example, if we have a dataset like:

Author	Book	Category
Dan Brown	The Da Vinci Code	Mystery
Dan Brown	Wild Symphony	Picture Book
Gillian Flynn	Gone Girl	Mystery

And we run the following query:

New Category

// Create single value for authors doing both mystery and picture books.

IF [Category]="Mystery" AND [Category]="Picture Book" THEN

    "Mystery/Picture Book"

ELSE

    [Category]

You might think here that Dan Brown will be listed as a Mystery/Picture Book since he has books in both categories. But this is not the case. Because the IF statement operates at row level only, the following will be the result:

Author	Book	Category	New Category
Dan Brown	The Da Vinci Code	Mystery	Mystery
Dan Brown	Wild Symphony	Picture Book	Picture Book
Gillian Flynn	Gone Girl	Mystery	Mystery

Another thing to remember when running IF statements is that they always return the same data type. For example, if you run the following query:

// Group the sales into three categories.

IF [Sales]<=1000 THEN

    1

ELSEIF [Sales]<=4000 THEN

    "Medium"

END

This will give the following error, “Expected type integer, found string. Result types from ‘IF’ expressions must match.” This simply means you can use either a string or integer value to return in the IF and ELSEIF statements.

Nested IF Statements

To perform complex queries and evaluate multiple conditions, IF statements can be nested. The following is the syntax for a nested IF statement:

IF [condition1] THEN 

    IF [condition2] THEN [value1] 

      ELSE [value2] 

    END

  ELSE [value3] 

END

For example, if we want to colour-code our products based on their category and profit, the statement will be as follows:

• Technology products with profit > $100: Green
• Technology products with profit <= $100: Yellow
• Furniture products with profit > $50: Blue
• Furniture products with profit <= $50: Red
• All other products: Gray

IF [Product Category] = "Technology" THEN
  IF [Profit] > 100 THEN "Green"
  ELSE "Yellow"
ELSEIF [Product Category] = "Furniture" THEN
  IF [Profit] > 50 THEN "Blue"
  ELSE "Red"
ELSE "Gray"
END

Tableau IF-ELSE Statement

The IF-ELSE Statement also tests for particular conditions. This variant is essentially similar to the IF Statement only with a minor difference. The difference is that the failed condition will not return the NULL value but instead will return the specified return value.

• If the conditional expression is satisfied (TRUE), the statement after the THEN keyword will be returned.
• When the conditional expression is not satisfied (FALSE), the statement after the ELSE keyword will be returned.

Syntax

A typical IF-ELSE Statement Tableau looks like this:

IF <Expression> THEN <True_Statement>
ELSE <False_Statement>
END

Let’s break this down and try to understand its various components.

• The ELSE keyword tells Tableau that an ELSE Statement is being performed.
• <False_Statement> is the value that will be returned if the conditional expression is FALSE.

Tableau IF-ELSE Statement Example

To execute the IF-ELSE Statement, you need to enter the code for IF-ELSE Statement in the Calculated Field as shown.

Now, you can observe in this example that the results are divided into 2 categories: Performing Good and Bad Performance. Similar to the previous example, “Performing Good” is returned whenever the condition is satisfied. “Bad Performance” specifies that the condition is failed and represents the profit that is less than 0. So, instead of Null, a stated statement “Bad Performance” is returned for a failed condition.

Tableau ELSEIF Statement

Unlike IF Statement Tableau, ELSEIF is capable of evaluating multiple conditional expressions. This function executes in a sequential manner and the conditions will only be evaluated if the previous IF or ELSEIF statement fails.

If the first condition expression is satisfied, it will execute the statement stated after the THEN keyword. However, if the condition is not satisfied, the next ELSEIF expression will be evaluated and executed.

Syntax

A typical ELSEIF Statement Tableau looks like this:

IF <Expression1> THEN <True_Statement1>
ELSEIF <Expression2> THEN <True_Statement2>
ELSEIF <Expression3> THEN <True_Statement3>
.....
ELSE <False_Statement>
END

Let’s break this down and try to understand its various components.

• The ELSEIF keyword tells Tableau that an ELSEIF Statement is being performed.
• <Expression2> represents the conditional expression and it will be evaluated only if <Expression1> is not satisfied.
• <True_Statement2> is the value that will be returned if <Expression2> is TRUE.
• However, if <Expression2> is not satisfied, Tableau will go on to evaluate <Expression3> and so on.

Tableau ELSEIF Statement Example

To execute the ELSEIF Statement, you need to enter the code for ELSEIF Statement in the Calculated Field as shown.

Now, you can observe in this example that the results are divided into 3 categories: High Profit, Low Profit, and Medium Profit. “High Profit” is returned whenever the first condition expression (profit greater than or equal to 2000) is satisfied. “Medium Profit” is returned when the first condition expression is not satisfied, and the second expression (profit between 1000 and 2000) is satisfied. However, if neither conditions are satisfied, and if the profit is below 1000, “Low Profit” will be returned.

Tableau IIF Function

The IIF Statement Tableau comes in handy while performing logical calculations. This function returns BOOLEAN results and categorizes them into 3 categories: TRUE, FALSE, and UNKNOWN. Similar to the IF Statement Tableau, IIF Statement returns a TRUE value when the conditional expression is satisfied, and a FALSE value for a failed condition. However, when the data contains values that yield an “unknown” result from the boolean comparison, the IIF Statement will either return the UNKNOWN value (if specified) or the NULL value. This typically happens when there are null values in the data set.

Syntax

IIF(test, then, else [unknown])

Let’s break this down and try to understand its various components.

• The IIF keyword tells Tableau that an IIF Statement is being performed.
• test represents the conditional expression.
• then is the value that will be returned if the conditional expression is TRUE.
• else is the value that will be returned if the conditional expression is FALSE.
• [unknown] is the unknown value returned if the conditional expression is neither TRUE nor FALSE.

Tableau IIF Statement Example

To execute the IIF Statement, you need to enter the code for IIF Statement in the Calculated Field as shown.

Now, you can observe in this example that the results are divided into 3 categories: Create Certificate, Do Nothing, and Null. “Create Certificate” is returned whenever the Letter Grade is A. “Do Nothing” is returned when the Letter Grade is anything (B, C, D, etc.,) but A. However, if the conditional expression is neither TRUE nor FALSE, i.e., when the Letter Grade row has missing grades, a “Null” value is returned.

IF Statement Tableau: Using Aggregate Functions

Tableau offers a range of built-in aggregation functions for more advanced calculations. Some of the most common functions include

Aggregate function	Meaning
SUM	Calculates the total sum of values in a field
COUNTD	Counts the number of distinct (unique) values in a field, ignoring duplicates
MAX	Finds the maximum value in a field
MIN	Finds the minimum value in a field
AVG	Calculates the average (mean) value of a field
MEDIAN	Finds the middle value in a field when the values are sorted in order

The syntax for using aggregate functions goes as follows:

<Aggregation>(IF [Condition] THEN [value] END)

For example, let us count the unique number of authors who have had a book release between 2020 to 2023 using the COUNTD function.

COUNTD(IF [Year] >= 2020 and [Year] <= 2023 THEN [Author] END)

This statement will return the distinct number of authors who had a book release between 2020 to 2023. If any author has multiple releases in those years, the author’s name will be counted only once.

Note: Always make sure to wrap your condition inside the aggregation(as in the above example) to avoid the “Cannot mix aggregate and non-aggregate comparisons or results in if statements” error message.

Tableau CASE Statement

In Tableau, the CASE statement is another way to perform conditional logic similar to the IF statement. The CASE statement allows you to define multiple conditions and their corresponding results.

The syntax for the CASE statement is as follows:

CASE [expression]

     WHEN [value1] THEN [result1]

     WHEN [value2] THEN [result2]

     ELSE [default result]

END

Let us look at the author’s example again for a better understanding.

CASE [Year]

  WHEN IN (2020, 2021, 2022, 2023) THEN '2020-2023 Book Releases'

  ELSE 'No Book Release in 2020-2023'

END

Though CASE statements cannot evaluate boolean expressions and cannot identify complex data patterns, they are much easier to perform and read than IF statements. If your data does not require complex evaluation, using a CASE statement in Tableau is a much better option.

Operators Available in Tableau

The IF and THEN functions are conditional expressions that return a boolean value – TRUE or FALSE. For support, these functions leverage comparison operators that help to compare two or more values. The six such comparison operators are tabulated below:

Operator	Meaning	Returns TRUE when
= or ==	Equal to	Both terms in the expression have exactly the same values.
<> or !=	Not equal to	The terms have different values.
>	Greater than	The first term has a larger value than the second term.
<	Less than	The first term has a smaller value than the second term.
>=	Greater than or equal to	The first term has either a larger value or the same value as the second term.
<=	Less than or equal to	The first term has either a smaller value or the same value as the second term.

Similarly, three logical operators are used to combine or modify conditions in logical expressions. The three primary logical operators are:

Operator	Meaning
AND	In order for the entire conditional expression to be true, the comparisons on the left and right side of the AND must both be true. If either of them is false, then the entire statement is false.
OR	For the entire conditional expression to be true, at least one of the comparisons on the left or right side of the OR must be true. The entire statement will only be false if both comparisons are false.
NOT	Unlike AND and OR, NOT is not used to combine multiple expressions. Instead, it is used on a single expression and basically returns the opposite of the expression’s result. In other words, if the expression is TRUE, NOT will make it FALSE and vice versa. NOT can often be avoided by using different types of comparison operators.

Order of Operations

Now that we have explored the examples and use cases of the IF statement in Tableau let us look at the order in which complex logical issues are carried out.

For example, in this particular case:

IF [Category]="Books" OR [Category]="Stationery Items" AND [Order Date]>=#01/01/2023# THEN

One might think that this expression will give all the orders that fall on or after January 1, 2023, and has a category of either “Books” or “Stationery Items”, but that is now how Tableau works. Tableau follows a specific order of operation, just like in mathematics. The following graphic shows the order of operations for logical statements:

Accordingly, a logical statement first computes the parentheses followed by NOT, AND, and OR. So let us now go back to the example to understand it better.

[Category]="Stationery Items" AND [Order Date]>=#01/01/2023#

This statement will give us all the items in the category “Stationery Items” with an order date on or after January 1, 2023.

The OR will then act as if the above statement is a singular expression. So, in English, the original statement will give us anything where 1) Category is “Books” OR 2) Category is “Stationery Items” and order date is on/after January 1, 2023. Book sales from 2022 will be included since they meet criteria # 1.

You can see how using multiple operators can become a bit confusing. Luckily, you can use parentheses to group the different comparisons. For example,

([Category]=”Books” OR [Category]=”Stationery Items”) AND [Order Date]>=#01/01/2023#

The parentheses will now force the statement to evaluate the conditions between them—is the category “Books” or “Stationery Items”? Only after that will the expression as a whole be evaluated along with the AND operator on the side.

Conclusion

Tableau is a prominent Data Visualization and BI tool that allows users to integrate various Data Sources and create attractive Charts, Dashboards, and Reports according to user-specified data that can be shared easily. Tableau doesn’t require any programming skills to operate, making it one of the most sought-after applications by people from various sectors. However, one must be familiar with basic logical functions such as IF Statement Tableau in order to generate actionable insights from raw data.

Here are some additional resources to learn more about Tableau and its functions:

• How to handle Tableau Null values using Null Functions? Simplified 101
• Tableau Conditional Formatting 101: Easy Hacks to Modify
• How to Build Tableau Line Charts: 5 Easy Steps

This blog takes you through the basics of Tableau IF Statements, touching on their types, components, and some basic examples. Tableau makes Business Analysis more efficient through intuitive, interactive, and easy-to-use services. Moreover, analyzing and visualizing your data by loading it from a Data Warehouse to Tableau can be cumbersome. This is where Hevo comes in.

visit our website to explore hevo

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of working with IF Statement Tableau in the comments section below.