9 Replies Latest reply on Aug 10, 2017 8:37 AM by Claire Douglas

    IS STR () Rounding Broken?

    William Peterson

      trying to combine two text fields into one ....

       

      IFNULL([RawDataStringValue],STR([RawDataFloatValue]))

      or

      IFNULL(ATTR([RawDataStringValue]),STR(ATTR([RawDataFloatValue])))

       

      a numeric value of .4 becomes .40000000001

       

      This seems very wrong.  are there any known fixes or workarounds?

        • 1. Re: IS STR () Rounding Broken?
          Shawn Wallwork

          You can try using the ROUND() function.

           

          --Shawn

          • 2. Re: IS STR () Rounding Broken?
            William Peterson

            If I use STR(ROUND(x,n)) where n is sufficiently large to cover the smallest possible values of x I get the same result.  Going to use two columns even though it's ugly.

            • 3. Re: IS STR () Rounding Broken?
              Jonathan Drummey

              What version of Tableau you are using, and what data source? I've seen some problems for live connections vs. extracts as well as just plain odd behavior that were fixed in 8.0/8.1.

               

              There are a couple of workarounds:

               

              1) The workaround suggested by Tableau at the time was to do string manipulation, such as this calc (haven't tested it recently to be accurate:

               

              IF RIGHT(STR(ROUND([floatValue],[decimalPlaces])),3) == "001" THEN

                  Left(STR(ROUND([floatValue]+.0001,[decimalPlaces])), Find(STR(ROUND([floatValue]+.0001,[decimalPlaces])),".") + [decimalPlaces])

              ELSE

                Left(STR(ROUND([floatValue],[decimalPlaces])),Find(STR(ROUND([floatValue]+.0001,[decimalPlaces])),".") + [decimalPlaces])

              END

               

              2) Depending on where you want to be putting the value, you might put both the text and numeric pills on the Text Shelf, Level of Detail, etc. then edit that Shelf so the values are next to one another. The Null text value won't be displayed, while the number one will. Here's an example where I've set up a set of fields to return Null when they shouldn't be the desired color, and then an actual value only when they should be the desired color:

               

              2014-10-13 11_30_24-Tableau - Board Dashboard.png

               

              Jonathan

              1 of 1 people found this helpful
              • 4. Re: IS STR () Rounding Broken?
                William Peterson

                Desktop is 8.1.6 64bit. Data is from an extracted, incrementally updated tableau server datasource.

                 

                The string manipulation method only works for the ~50% of numbers that rounded up to .4000000001, but fails for the numbers that are rounded down to .2999999999

                 

                Combining the two as text marks, color coding with pass/fail, with a constant 'Result' for a header in the Column looks fine. I'll do this. It's an acceptable workaround for my specific problem.

                • 5. Re: IS STR () Rounding Broken?
                  Jonathan Drummey

                  I had that ...9999 problem as well, the string manipulation could also add in an ....OR RIGHT([rounded number],3) == "999"... but that whole calculation is a kludge. Please submit this to support, it's a recurring problem that just shouldn't be there.

                   

                  Jonathan

                  • 6. Re: IS STR () Rounding Broken?
                    kettan

                    Please submit this to support

                    Is this different than case # 258703?   Was case # 258703 solved?

                     

                    Source:   Re: Error when converting rounded number to string

                    • 7. Re: IS STR () Rounding Broken?
                      Jonathan Drummey

                      I never got an official-type notification that case #258703 was solved, in

                      my test workbook that particular problem was fixed in a later release.

                       

                      On Mon, Oct 13, 2014 at 2:25 PM, kettan <

                      • 8. Re: IS STR () Rounding Broken?
                        kettan

                        Thanks. Then I also hope current issue will be reported to support.

                        • 9. Re: IS STR () Rounding Broken?
                          Claire Douglas

                          This string manipulation got me mostly there, but still left the banker's rounding, going to the nearest even digit.  Following is what I came up with to round to the nearest 2 decimals:

                           

                           

                          left(str(round([float Value]*100)),len(str(round([float Value]*100)))-2)

                          +"."

                          +right(str(round([float Value]*100)),2)

                           

                          If you need to round to a different number, you can adjust multiplying by 100 to and the 2, which returns the values to the right of the decimal.  The following should work for three decimals (hasn't been tested)

                           

                          left(str(round([float Value]*1000)),len(str(round([Price Split 1]*1000)))-3)

                          +"."

                          +right(str(round([float Value]*1000)),3)