Boolean in Tableau – true or false?

//

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.

words true and false in neon lights
Boolean is either True or False

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:

SUM([Profit])>0

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.)

[isProfitable]: SUM([Profit])>0

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):

DateSalesProfit
01/01/20183637
01/04/20189796
01/07/20183649
01/10/20181885
01/01/20197196
01/04/20194161
01/07/20197672
01/10/20193762
01/01/20201428
01/04/20207582
01/07/20201468
01/10/20206673

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

Which returns:

DateSalesProfitisCYisPY
01/01/20183637FALSEFALSE
01/04/20189796FALSEFALSE
01/07/20183649FALSEFALSE
01/10/20181885FALSEFALSE
01/01/20197196FALSETRUE
01/04/20194161FALSETRUE
01/07/20197672FALSETRUE
01/10/20193762FALSETRUE
01/01/20201428TRUEFALSE
01/04/20207582TRUEFALSE
01/07/20201468TRUEFALSE
01/10/20206673TRUEFALSE

These flags are the simple to use to calculate sales for this year:

[SalesCY]: [Sales]*INT([isCY])

Or profit for the prior year:

[ProfitPY]: [Profit]*INT([isPY])

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.

tableau boolean calculated field with the formula 1=1
A boolean calculated field in Tableau (the // is a comment)

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

tableau calculated field with boolean = "true" to cause an error
Creating the Can’t compare boolean and string values error in Tableau

To resolve this error completely remove ‘= “True”‘ from the formula OR remove the quotation marks around the word True.

correctly referring to boolean in a tableau calculated field
Fix the Tableau Can’t compare boolean to other values error

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

tableau calculated field with boolean = 1 to cause an error
Creating the Can’t compare boolean and integer values error in Tableau

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!

7 thoughts on “Boolean in Tableau – true or false?”

  1. Some great tips here!! I love them. And they are largely applicable to anything that uses booleans, not just Tableau. I will admit that I tend to write out everything in detail in a calculated field, but only because I’ve observed that many people do not truly understand the nature of booleans. As most of my workbooks are shared for public consumption or will eventually be owned by someone else at work, I write out the full statement just so they understand what it’s doing. Thanks for writing this blog and helping to provide the base knowledge people need–maybe that will allow me to eventually be a bit less wordy 😉

    Reply
    • Appreciate the comment Ken. And agreed, this applies to most software. It was actually an Excel developer many years ago who helped me to better understand boolean by shortening my convoluted IF statements!

      Reply
    • Ken, one interesting thing to do in your workbooks would be to use the Boolean formula and comment the explicit one. This way people will understand them and your workbook will be more performant

      [Year] = [Max_Year]

      // IF [Year] = [Max_Year] THEN “Yes” ELSE “No” END

      Reply
  2. I usually use booleans and change aliases so filters and labels (when I need them) show in a more explicit way

    Does that affect performance? I would say no because internally it is still a boolean but I want to confirm

    Reply
    • Great question, not something I have tested, but I agree with your conclusion; if it’s a boolean “under the hood” then it should perform like a boolean regardless of alias.

      Reply

Leave a Reply to Ken Flerlage Cancel reply