Tableau IF Functions

This post explains the different options to write IF statements in Tableau. This includes IF, IIF, and CASE, with a brief mention of IFNULL.

The full contents of the article are:

• IF statements in Tableau
• IIF statements in Tableau
• IF vs IIF
• Aggregate and non aggregate error
• CASE statement in Tableau
• Limitations of CASE vs IF
• Boolean instead of IF

Tableau Logical Functions

IF, IIF and CASE statements are part of the Tableau Logical Functions group.

Other logical functions, such as ISDATE and ISNULL, are covered elsewhere.

Before getting into the detail of Tableau logical functions, in general:

• IF statements are more flexible. They allow boolean logic in the test, and allow testing conditions in dimensions, measures and aggregates.
• CASE statements can offer better performance when compared to their closest equivalent, the nested IF statement.
• CASE statements are more restrictive than an IF statement, but they still serve a purpose
• IF and CASE statements can nest within each other – i.e IF test THEN CASE other test…END END

IF in Tableau

Firstly, a standard IF statement. More verbose than the IIF function but, at least in my opinion, easier to read.

Use an IF statement to test for values within a dimension, values within a measure or even aggregated values – such as SUM(Field)>X

The syntax of an IF statement is:

IF test THEN action when test is true ELSE action when test is not true END

ELSEIF also fits into this for more complex tests:

IF test THEN action when test is true ELSEIF next test THEN action when test is true ELSE action when test is not true END

Don’t forget the IF statement always terminates with END – without that you’ll get an error.

The ELSE part of the statement isn’t essential. If omitted, all values falling into ELSE return NULL.

IF statements require boolean logic

A big plus about the IF statement is that it accepts boolean logic, while a CASE statement doesn’t. This is a question in some Tableau certification tests…but what does it mean?

Boolean is true or false, so any test that returns True or False works within an IF statement. CASE statements only test if a value is equal to another value, they don’t test for greater than, or other comparative tests.

Example IF statement tests can include [Sales]>1000; [Sales]>[Budget]; [Colour] = “Red”; etc. Anything that returns True or False. All of these tests are boolean; Sales either are or aren’t above 1000, the Colour either is Red or isn’t, etc.

However, IF statements cannot output boolean values. It will cause an error. Fortunately, there is a simple solution to the Tableau error “Cannot use boolean type in IF expression”. Convert the boolean output to an integer.

Tableau IF statement with a dimension

An IF statement can test a value/values in a dimension. For example, group different spellings of Mr:

IF [Title] IN ("Mr","Mr.","Mister") THEN "Mr" ELSE [Title] END

With dimensions, the IF test statement can also accept different text functions – LEFT, RIGHT, LEN, etc – or just be a simple equals sign. As long as the results evaluates as boolean – True or False – the IF statement can use it.

Tableau IF statement with a measure

An IF statement also works with a measure – both aggregated and not aggregated. Testing a non-aggregated measure is a row-level comparison. For example, perhaps positive and negative values should have a different label:

IF [Value] > 0 THEN "Revenue" ELSE "Cost" END

Tableau IF statement with an aggregation

Testing against an aggregation is a big plus about the IF statement. It is commonly used to group the results of YoY Growth, or performance against a target. Assuming the [YoY] field is an aggregate, such as SUM([Year1])/SUM([Year2])

IF [YoY]>0 THEN "UP" ELSE "DOWN" END

Another common use is using a nested IF statement, for example grouping Sales into High, Medium and Low:

IF SUM([Sales]) > 100000 THEN "High"
ELSEIF SUM([Sales]) < 1000 THEN "Low"
ELSE "Medium" 
END

Note the order of these tests – as soon as the statement returns True, it exits and doesn’t test any further. Therefore, it’s important to test in the correct order.

As an example, consider the following statement:

IF SUM([Sales]) > 10 THEN "Medium"
ELSEIF SUM([Sales]) > 50 THEN "High"
ELSE "Low"
END

With the above formula, sales of 100 would group as “Medium”. This is because 100 > 10, which is the first test, so the statement is True and Tableau exits the formula. It doesn’t test against the next line of the formula, to test if 100>50.

IIF in Tableau

IIF is also known as the Immediate If. It’s a shorter way to write a simple IF statement. For more complex IF statements, especially if nesting or needing a form of ELSEIF, use the standard IF statement above.

This is similar to an Excel IF statement and I have observed those coming from Excel to Tableau seem to prefer this.

The syntax for IIF is:

IIF(test,action when true, action when false,[unknown])

Use this for simple IF logic, for example:

IIF([Food]="Peanut","Nut","Not a Nut")

IIF statements can also be nested, although they can be hard to read.

IIF(test,action when true, IIF(other test, action when true, action when false))

Similar to a standard IF statement, these can also test dimensions, measures and aggregations.

I won’t run through specific formula examples, simply alter the examples in the IF statements to fit the IIF syntax.

However, it’s worth looking at some of the differences between IF and IIF.

IF vs IIF in Tableau

Although these two logical functions are similar, there are differences. The key difference is the IIF statement handling unknown values.

The simplest way to explain is by an example. The field [Sales] contains three values: 15; Null; 65. The test is if Sales > 50.

IIF([Sales]>50,"High","Low","No value")

The equivalent formula using an IF statement:

IF [Sales]>50 THEN "High" ELSE "Low" END

The two If statements return slightly different results:

Notice the results of the Null sales value.

The IIF statement tests if this value is >50. The IIF statement doesn’t know if Null is greater than or less than 50. Therefore, it triggers the Unknown section of the IIF statement.

As the IIF function is told how to handle unknowns, it returns the text “No value”. With nothing specified, it returns Null.

The IF statement tests only if >50. It doesn’t return True, so the Tableau IF statement returns the False value.

Both the IF and IIF first check if the test is true; but the IIF then tests if the value is False. IF doesn’t test for the False component – it treats everything not True in the same way.

IIF handles those items that aren’t True or False (or Unknown) differently to IF.

Cannot mix aggregate and non aggregate comparisons or results in if statements

One word of warning – when testing an aggregation in an IF statement, ALL of the statement referring to fields in the data require aggregating. Otherwise you’ll come across the famous “Cannot mix aggregate and non aggregate comparisons or results in if statements” error message.

A formula like this will produce the aggregate/non aggregate error:

IF [Category]="Furniture" THEN SUM([Sales]) END

Sales are aggregated, while Category isn’t. Tableau expects both side to be aggregated or not aggregated. The simple solution for a simple example, like the above is to move the SUM outside of the IF statement:

SUM(IF [Category]="Furniture" THEN [Sales] END)

Moving the aggregation outside of the IF statement solves the majority of aggregate and non-aggregate comparison errors.

I saw an example in a forum producing this error:

IF [Over] >= 15 THEN (SUM([Batsman Runs])/COUNT([Batsman]))*100 END

Remember, all or none of the statement should aggregate. In this case, [Over] is not an aggregation, it’s a dimension, while the IF statement result is an aggregate. The solution to this is to bring [Over] into the constituent parts of the formula:

(SUM(IF [Over] >= 15 THEN [Batsman Runs] END)/COUNT(IF [Over] >= 15 THEN [Batsman] END))*100

The aggregate / non-aggregate error is also often seen when using a field that’s an aggregation.

A common example if using a parameter to enable the user to choose their measure. Perhaps the user can either select to view Sales, which is a standard measure, or Year over Year, which is often a calculation (i.e. SUM([Year1])/SUM([Year2]). The YoY measure is already aggregated, while Sales needs to be aggregated.

IF [ChosenMeasure] = "Sales" THEN [Sales] ELSE [YoY] END

To fix this type of aggregate and non-aggregate error, this time bring the aggregation of Sales into the IF statement – the opposite of the previous example! It’s not possible to “un-aggregate” the year on year calculation, therefore the solution is to aggregate the Sales side of the calculation.

CASE statement in Tableau

A CASE statement tests for a specific value. It doesn’t support the more flexible boolean logic of an IF statement; greater or less than, AND / OR, etc. Only equals.

Also, it can’t test an aggregation, although it can output an aggregation.

The Case statement is great for longer logic, for example categorising items, where any form of IF statement would become too long and unwieldy.

A well written CASE statement is easier to read than a large IF statement.

A CASE statement can provide better performance than an IF statement. An IF statement evaluates each record individually, while a CASE statement is more targeted, so can be more efficient.

For those coming from a SQL background, the Tableau CASE statement doesn’t offer the same flexibility. The syntax is slightly different and more restrictive than SQL.

The Tableau CASE statement syntax is:

CASE test WHEN value THEN something WHEN another value THEN do something else WHEN ... ELSE ... END

The ELSE isn’t required for the statement to work. However, without it, anything falling into this bucket will return Null.

For example, it can act as a formulaic way to create groups in Tableau:

CASE [Food]
WHEN "peanut" THEN "Nut"
WHEN "almond" THEN "Nut"
WHEN "apple" THEN "Fruit"
ELSE "Other Food"
END

However, it offers more flexibility than creating simple groups. For example, [Food] (as above), can be a formula instead, such as LEFT([Food],3). Meaningless for this example, there are situations where this flexibility is useful.

Although CASE statements can’t test an aggregation, they can return an aggregation. This is one way to set up custom sorting of a table in Tableau. An example of returning an aggregate in a CASE statement is:

CASE [SortField]
WHEN "Sales" THEN SUM([Sales])
WHEN "Profit" THEN SUM([Profit])
END

But the following won’t work as the CASE statement can’t test an aggregate function:

CASE SUM([Sales])...WHEN 100 THEN...END

Limitations of IF vs CASE in Tableau

IF vs CASE statements has been discussed extensively over the years. It’s not always clear which is better, however, generally a CASE statement is more limited than IF.

Although in certain situations a CASE statement can provide better performance and readability than an IF statement, the CASE has the following limitations compared to IF and IIF:

• a CASE statement checks for an exact value; it can’t accept boolean logic like the IF statement
• a CASE statement can’t test aggregations
• a CASE statement can only test for a single value – it can’t test against multiple values or using AND/OR

An example of testing multiple values in an IF statement, which isn’t possible using a CASE statement:

IF [Category]="Furniture" AND [Country]="Spain" THEN do something END

IFNULL in Tableau

IFNULL is a special function that checks if a value is null and, if it is, converts it to something else. Read this article for a detailed description of the Tableau IFNULL function.

Simplify IF statements with boolean

Before looking at the various options of Tableau logical functions, which includes IF, IIF and CASE, firstly decide if it’s necessary to use an IF statement.

Often, using simple boolean logic is a more efficient and simpler to write alternative to an IF statement.

For example, common IF statements that work better with boolean are:

IF [X] = Y THEN "Yes" ELSE "No" END

IF [X] = Y THEN "True" ELSE "False" END

IF [X] = Y THEN 1 ELSE 0 END

Statements of this type are frequently written by developers less familiar with boolean – plus there’s an argument it’s easier to read. Replace these statements with:

[X] = Y

Yes, as simple as that. If it’s necessary to have it return 1 or 0, then use the INT function:

INT([X] = Y)

Boolean IF statements can be more complex, incorporating AND / OR functions:

[DateField]>TODAY() AND [X] = Y

Boolean formulas can’t replace all IF statements, but are useful to have in the toolbox!