This post discusses three tests I did using Boolean expressions in Tableau that evaluated 64-bit operands for equality. I interpret the test results from the test and show what the largest integer bit integer that Tableau does support.

I posted on January 29th about data corruption in R when dealing with 64-bit integers. The takeaway from that post was that analysts have know the limitations of the tool that they are using. Well, it turns out that Tableau is also a tool that has limitations when dealing with 64-bit integers. The nature of the problem is somewhat similar to the limitation in R, but also not as bad as the limitation in R. This post discusses the limitation. This post discusses three tests I did using Boolean expressions in Tableau and how to interpret the results.

A users on the Tableau Community Forum posted about Tableau handling integers of a lenght of 150+ digits. Wow. Ok. I found this interesting paper on super long integers in cryptography.

That above post in the Tableau forum and this one got me thinking about whether or not Tableau supports 64-bit integers.

Test 1 This calculation compares two variables that are assigned the 64-bit integer operand values of 100000000000000000 and 100000000000000001 respectively. The Boolean expression [100000000000000000] == [100000000000000001] correctly evaluates to False.

For readers unfamiliar with variables in R, they are called calculated fields. Enclosing the variables in square brackets is Tableau syntax.

Test 2 This calculation compares two, calculated 64-bit integer operands for equality. The test (2^63)-1 == (2^63)-2 incorrectly evaluates to True.

Test 3 This calculation compares two, 64-bit integer operands for equality. The result of the Boolean express 100000000000000000 == 100000000000000001 correctly evaluates to False.

Here is a dashboard that summarizes the results from my tests:

How do I interpret these results? If you can get your data into Tableau as a 64-bit integer, it looks like you might be ok. But, I need to test this using other data sources. For sure, any data source that relies on the Microsft JET engine is going to result in data corruption as Jet is limited to 32-bit only. If you use a calculated field in Tableau to create an integer constant, and later use that constant in other expressions, it looks like you’ll be ok. Test 1 confirms this. If you compare two integer constants directly, you’ll be ok. Test 3 confirms this. But, if you compare two, calculated values, the result is not correct. It seems that these calculated values are stored as floats. Comparision of two floats, when one or represent values that exceed the precision of the float, is always going to result in data corruption.

How big can you go in Tableau? It looks like the 32-bit, signed integer if you want to be safe, or until Tableau releases technical information about precision limitations of their Number data type for integers and floating point numbers. Why do I say 32-bit? Because it’s a safe bet. Here are some other tests:

Test 4 The expression (2^54)-1 == (2^54)-2 evaluates to False. This is correct.
Test 5 The expression (2^55)-1 == (2^55)-2 incorrectly evaluates to True.
Test 6 For negative numbers, the expression -(2^53) == -(2^53)+1 evaluates to False, the correct result.
Test 7 But, the expression -(2^54) == -(2^54) + 1 evaluates to True, an incorrect result.

But, then:

Test 8 The calculated fields abs(-(2^53)) and (2^53)-1 return the same float value, and, when formatted as integers, the same integer value (as expected). Yet, the expression abs(-(2^53)) == (2^53)-1 returns False, the correct result. If the floats have insufficient precision, how is it that the result of this expression is correct? Is Tableau doing a conversion to their String data type?

I’m not sure why Tableau doesn’t support 64-bit signed integers. They do have just the one, numeric data type, Number, and are probably using the bits to store the length of the mantissa.

These tests were done using Tableau Desktop 8.1.4 (8100.14.0213.2024) 64-bit on a system running Windows 7 Enterprise SP1 64-bit.

Wrap

Same as my previous post…be careful. Know that Tableau doesn’t throw an overflow message or give you a warning. Know the limitations of the tool you’re using. Identify and experiment with each workaround and decide which one is best for the data problem you are trying to solve.

Leave a reply

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> 

required