I have encountered a very strange behavior with Tableau (8.1.6) adding phantom digits to a rounded number when converting into a string. The problem only appears when the number being rounded is a whole number and the rounding is to 6 significant digits or greater. I’m wondering if anyone has an explanation or if perhaps this might be a bug.
For reasons related to creating a reliable parameter-driven sort option, I have a calculated field that subtracts the result for a parameter-driven calculated field from 1 trillion, and turns the result into a string. The result of the subtraction is rounded before converting to a string, in order to avoid improper sorting with strings of different length. Works great, until rounding is at 6 significant digits and is drawing upon a whole number (rather than a percentage). With integers, I get strange results.
As an example, consider the formulas below.
[Rounded String]: STR(Round(1000000000000-([Dash_Measure_2]),6))
[Rounded Number]: Round(1000000000000-([Dash_Measure_2]),6))
In one output for 3 different results for [Dash_Measure_2], these formulas generated the results below (shown here as comma delimited values).
Rounded String: 999999999804.000000, 999999999772.999878, 999999999775.000122
Rounded Number: 999999999804.000000, 999999999773.000000, 999999999775.000000
We can see that Tableau is adding or subtracting 0.000122 from the actual result before converting to a string in some cases. I can’t see any rhyme or reason to why Tableau sometimes gets it right and why Tableau subtracts sometimes and adds other times. The threshold for the issue is 6 significant digits or greater. At 5 significant digits or less, the results shown above all show a string of zeros after the decimal place. Above 6 significant digits, the appendages are still there, just longer. E.g., at 7 significant digits, .000122 becomes .0001221. And I have just noticed that, at least in one case, Tableau can flip from an addition operation to a subtraction operation for the same result when expanding from 6 to 7 digits.
Has anyone experienced this? Any thoughts on why this might be happening?
(Note: the workbook is sensitive, so I can’t share it. Apologies. Also, I should note that [Dash_Measure_2] is an aggregation, pulling from parameter-driven nested if/case-type calculations. In one case when I am seeing this seeing this, it’s pulling from a very simple formula:
Where [Head Count] is a very simple calculated field equal to 1.
However, I am also seeing it with fields that can contain decimals, when the result is a whole number.