The Boolean data type is the most efficient for Tableau to process. Yet it is very under-used.
It seems many developers do not make proper use of the Boolean data type in Tableau. Perhaps it is misunderstood.
This article will tell you what you need to know about the Boolean in a Tableau context.
On various Tableau forums I see developers, from Junior to Zen Master, using a long-winded formula when a simple Boolean would do the same job faster.
This post is to encourage developers to use more Boolean!
Although this is written for Tableau, the exact same also applies to Excel, should any Excel developers read this article.
What is the Boolean data type?
In Tableau, Boolean is either True or False. It can only be one of those values. In some software a tri-state data type – True, False, Null – can sometimes be mistakenly referred to as boolean, for example a SQL bit data type.
In terms of performance, Boolean is as good as it gets.
True or False is also represented as 1 or 0, where 1 is True and 0 is False. (Note in some software True is represented as -1.)
This data type is the quickest for computers to process. Using Boolean values in calculated fields gives faster Tableau performance compared to numeric or string calculations.
Examples of using Boolean in a Tableau calculation
Perhaps it’s the terminology of True or False that deters people from using it. But another, and perhaps less intimidating way to view it is as a simple Yes or No.
For example, maybe you only want to see profitable stores. Framing this as a question in plain English helps define the Boolean calculation:
“Is profit greater than zero?” – “Yes” or “No”
In Tableau that becomes the boolean calculation:
The result being True (or Yes) if it is profitable.
However, what I often see is that written:
IF SUM([Profit])>0 THEN "Profit" ELSE "Loss" END
Which is actually significantly longer and more complex to write…but seems easier for most developers to understand that over a simple Boolean.
Boolean example 2
Another example, create a boolean field called [isProfitable]: (Note you don’t set the data type, Tableau does this automatically.)
Some would then use this as follows:
IF [isProfitable]=True THEN "Profit" ELSE "Loss" END
The “=True” is not needed. Remove it and the result is the same.
Think about the IF statement:
IF something is true THEN do something ELSE do something else.
The first part of the IF statement is only deriving if something is True or False. Therefore, the =True is unnecessary, it’s implicit in the IF statement anyway.
Boolean Example 3
Another example I saw recently:
IF [Field] = "A" THEN True ELSE False END
Again, overly complicated. The simple boolean equivalent is:
[Field] = "A"
It either does equal A, so returns True, or it doesn’t equal A, so returns False.
Boolean Example 4
Similarly, I saw this:
IF [task] = 'Finish' THEN 1 ELSE 0 END
Far simpler to write:
INT([task] = 'Finish')
Boolean example 5
Another example (copied directly from a forum):
If CONTAINS([Field],"2-Wheel Drive, Front") = true then 1 end
Which is the same as:
INT(CONTAINS([Field],"2-Wheel Drive, Front"))
(Ok, it’s not quite the same; the initial formula returns NULL if it doesn’t contain “2-Wheel Drive, Front”, while the boolean version returns 0.)
Converting Boolean to a number
In some of the previous examples, notice I wrapped the Boolean output in the INT() function.
The output of a Boolean is True or False. The INT function converts True to 1 and False to 0.
This is very useful. For example, use this to solve the common error message “Cannot use Boolean type in IF expression“
Use Boolean for Year on Year, Month on Month, Period vs Period calculations
Another good example is to calculate this year and last year numbers.
Consider a data set such as (deliberately small for the example):
Assume the current year is 2020 and we want to compare this year with last year. That means identifying this year and identifying last year (2019).
We can use Boolean fields for this by creating simple flags, to use in other calculations.
For example the current year (isCY = is Current Year):
[isCY]: YEAR([Date]) = YEAR(Today())
For the prior year:
[isPY]: YEAR([Date]) = YEAR(Today())-1
These flags are the simple to use to calculate sales for this year:
Or profit for the prior year:
How does this work?
Simple – it multiplies all of the values by either 1 or 0. Therefore they either remain as they are ([Sales]*1 = [Sales]) or they are disregarded ([Sales]*0 = 0).
Therefore the formula for aggregated Year on Year is::
SUM([SalesCY])/SUM([SalesPY]) - 1
No need for table calculations or filters, the year on year change is handled within the Boolean calculations.
Common boolean error messages in Tableau
There are a number of errors involving Boolean that appear regularly.
- Can’t compare boolean and string values
- Can’t compare boolean and integer values
To replicate and resolve these issues, firstly create a boolean calculated field.
To create a boolean calculated field in Tableau, create a new calculated field and enter the formula 1=1.
1 equals 1, so this formula returns True.
To return False alter the formula to 1=0. Any formula returning True or False is a boolean calculated field.
Can’t compare boolean and string values
To replicate this error, create a new calculated field using the above boolean calculated field. Enter the formula:
IF [boolean] = "True" THEN "T" ELSE "F" END
To resolve this error completely remove ‘= “True”‘ from the formula OR remove the quotation marks around the word True.
Can’t compare boolean and integer values
To replicate this error create a new calculated field using the boolean calculated field. Similar to above, enter the formula:
IF [boolean] = 1 THEN "T" ELSE "F" END
To resolve this error either remove =1 OR wrap [boolean] in INT() to convert it to a number – with the first option the better choice.
Tableau Boolean functions
Some of the Tableau functions, used in calculated fields, are boolean:
- ENDSWITH – used to see if a string ends with another string
- ISDATE – used to test if a string is also a valid date
- ISFULLNAME – used on Tableau Server or Tableau Online to test if the logged-in user’s full name matches the specified full name
- ISMEMBEROF – used on Tableau Server to manage permissions, it checks if the logged-in member is a member of a specified Tableau Server group
- ISNULL – checks if a given field is Null
- ISUSERNAME – similar to ISFULLNAME, it checks if the logged-in user’s username matches the specified username
Now you know how to properly use the boolean data type in Tableau, remember to:
Use more boolean in your Tableau calculations!
If you want faster and more efficient Tableau workbooks, the recommendation is simple:
Use more Boolean and improve your Tableau dashboard performance!