1 2 Previous Next 16 Replies Latest reply on Apr 18, 2019 1:40 AM by Jonathan Drummey

    Error when converting rounded number to string

    Michael Crow

      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:

       

      SUM([Head Count])

       

      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.

        • 1. Re: Error when converting rounded number to string
          Jim Wahl

          I have run into this once or twice before, when trying to make custom text messages from numerical fields. I don't remember the specifics, but I believe after spending too much time trying to debug it I took a different approach.

           

          If your primary goal is custom sorting based on a measure, maybe you don't need a string?

           

          Jim

          • 2. Re: Error when converting rounded number to string
            Michael Crow

            Hi Jim,

             

            Thanks for your response. Good question: I have to convert these measures to strings because they are called by an IF function that can also call another, default sort option that is string-based, and it does not appear that I can combine string and non-string calculated fields in the same logical statement. I believe I could make everything work by converting that string-based default sort into a numeric field, but that would require rebuilding and/or extra ongoing maintenance of hardwired aspects of the workbook that I would like to avoid. RANK () was another promising sort option I tried, but could not get all my sorts to work properly in all the different permutations of the dashboard, and I felt that even if I got it working it would introduce too much additional complexity on top of an already very complex dashboard.

             

            One other thought I have would be to avoid the rounding when the result is a whole number, but couldn't think of a way to programmatically determine when a result is an integer and when it is not. I didn't see any functions that do that. Any ideas?

             

            Mike

            • 3. Re: Error when converting rounded number to string
              Jim Wahl
              programmatically determine when a result is an integer and when it is not.

              Maybe: INT(x) == x

               

              RANK () was another promising sort option I tried, but could not get all my sorts to work properly in all the different permutations of the dashboard

              It seems like you might also be able to do this with INDEX(), which is similar to RANK(). You might have a field like:

              Sort =
              IF      [Sort by ] == "Profit" THEN SUM(Profit)
              ELSEIF  [Sort by ] == "Sales" THEN SUM(Sales)
              ELSE INDEX()
              END
              

               

              Create another calculated field for the string options:

              Sort String = 
              IF     [Sort by ] == "Customer Segment" THEN [Customer Segment]
              ELSEIF [Sort by ] == "Container" THEN [Container]
              END
              

               

              Now when you add Sort to the view---usually as the left-most column---you can click the pill > Edit Table Calc. Compute using Advanced > Move all of the dimensions you're sorting over to the right, Addressing box and then in the Sort area in the bottom select the MIN(Sort String) field. In your case, MIN() == MAX().

               

              It sounds like you have a complex dashboard, so I'm sure it's not going to be quite as easy as the above, so good luck.

               

              Jim

              1 of 1 people found this helpful
              • 4. Re: Error when converting rounded number to string
                Michael Crow

                Jim,

                 

                Thanks again. I knew I must be missing an Integer function. I think that could work for me, but would still leave me with string versions of rounded decimals. I'm inclined to stay away from converting any rounded numbers to strings until I understand what the underlying problem is. Anybody else out there have ideas?

                 

                As for the suggestion about Index(), thanks for that alternative. I have my doubts based on my experience with trying Rank(), but it's worth adding to the list. I appreciate that you are shooting in the dark a bit since I can't share the workbook.

                 

                Side question: would you mind telling me what tool you had your calculated fields displayed in? The screenshots are intriguing.

                 

                Thanks again,

                 

                Mike

                • 5. Re: Error when converting rounded number to string
                  Jim Wahl

                  You should submit this to Tableau customer support (Get to the Answer | Tableau Software). It appears to be a bug, and they may have it logged somewhere, and might have a recommended workaround.

                   

                  The calculated fields above are fomatted using the advanced editor, where there's an option to format as SQL, javascript, ...

                  • 6. Re: Error when converting rounded number to string
                    Jonathan Drummey

                    I ran into what sounds like the same problem a year ago on v7 when using STR(ROUND([some aggregate])), the case # was 258703 and was submitted as a bug.

                     

                    The workaround I was given from Tableau is the following calc that does a bunch of string testing:

                     

                    If right(STR(ROUND([Profit Ratio],ATTR([Decimal Places]))), 2) = Str("99") then
                        Left(STR(ROUND([Profit Ratio]+.0001,ATTR([Decimal Places]))), Find(STR(ROUND([Profit Ratio]+.0001,ATTR([Decimal Places]))),".") + ATTR([Decimal Places]))
                    Else
                      Left(STR(ROUND([Profit Ratio],ATTR([Decimal Places]))),Find(STR(ROUND([Profit Ratio]+.0001,ATTR([Decimal Places]))),".") + ATTR([Decimal Places]))
                    End
                    

                     

                    In my case, the strings were ultimately only about doing some complicated formatting and I was able to avoid this entirely by taking advantage of 8.0's new ability to have multiple pills on the Text/Label Shelf, and then have individual custom number formats for each pill.

                     

                    Another workaround that has sometimes been possible is to use a RAWSQL function to format results (in my case, I was using a Tableau Data Extract so that wasn't feasible).

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 7. Re: Error when converting rounded number to string
                      Michael Crow

                      Thanks, Jim. I imagine support will want to see the workbook, and I will need to check with my client's comfort level with that. Thanks for the tip on the advanced editor. I had not noticed that.– Mike

                      • 8. Re: Error when converting rounded number to string
                        Michael Crow

                        Jonathan,

                         

                        Thanks for sharing this.I will look at this workaround more carefully. It's also helpful to have the case number to share with support. Did they that they understand the boundaries of this bug? What worries me is the possibility that the boundaries aren't understood, and a workaround will only address problems that have been observed (and unobserved problems may yet be lurking).

                         

                        Thanks, Mike

                        • 9. Re: Error when converting rounded number to string
                          Oscar Jara

                          Hi Erveryone... I have the same issue and this bug is still in Vr 9.0... what should we do?

                          • 10. Re: Error when converting rounded number to string
                            Jonathan Drummey

                            Hi Oscar,

                             

                            There are some comments on ROUND function returns strange results about this, please refer to that. (For everyone else on this thread, it's not a bug).

                            • 11. Re: Error when converting rounded number to string
                              Jonathan Drummey

                              Hi,

                               

                              I have an update to this thread with a more accurate calculation, details are here: Re: ROUND function not working as expected.

                               

                              Jonathan

                              • 12. Re: Error when converting rounded number to string
                                Jeffrey Shaffer

                                Posting a response for future users that encounter this problem. This is the solution that I have found works.

                                 

                                Wrap the field in MAX() or AVG(). Instead of str(round(field])) use str(round(max([field]))).

                                • 13. Re: Error when converting rounded number to string
                                  Jonathan Drummey

                                  Hi Jeffrey,

                                   

                                  I’ve run into the rounding problem with both record level and aggregates so the MAX() or AVG() is not guaranteed to work.

                                   

                                  Jonathan

                                  • 14. Re: Error when converting rounded number to string
                                    Jonathan Drummey

                                    Adding my notes to your notes.

                                     

                                    I’ve run into the rounding problem with both record level and aggregates so the MAX() or AVG() is not guaranteed to work.

                                     

                                    Jonathan

                                    1 2 Previous Next