9 Replies Latest reply on Jan 28, 2019 9:06 AM by Jonathan Drummey

    ROUND function not working as expected

    Anjali Sn

      Hi,

       

      I have a column in the database whose values are float values. But when I try to write a calculated field on that column, using ROUND function, it's not rounding to the required number of decimals.

       

      Like I'm doing something like: STR(ROUND([column],2))

       

      And getting the value of it as :

      85.709999999999994%

       

      Why is that???

        • 1. Re: ROUND function not working as expected
          Deepak Rai

          STR(ROUND(SUM([column]),2))

          • 2. Re: ROUND function not working as expected
            Shinichiro Murakami

            It's long time discussions.

             

            Here is one post relating to that

            .Error when converting rounded number to string

             

            It's not a bug, but troublesome...

             

            Shin

             

             

            6. RE: ERROR WHEN CONVERTING ROUNDED NUMBER TO STRING

            Jonathan DrummeyData Monarch

             

            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:

             

             

            1. If right(STR(ROUND([Profit Ratio],ATTR([Decimal Places]))), 2) = Str("99") then 
            2.     Left(STR(ROUND([Profit Ratio]+.0001,ATTR([Decimal Places]))), Find(STR(ROUND([Profit Ratio]+.0001,ATTR([Decimal Places]))),".") + ATTR([Decimal Places])) 
            3. Else 
            4.   Left(STR(ROUND([Profit Ratio],ATTR([Decimal Places]))),Find(STR(ROUND([Profit Ratio]+.0001,ATTR([Decimal Places]))),".") + ATTR([Decimal Places])) 
            5. 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

             

             

            • 3. Re: ROUND function not working as expected
              Chris McClellan

              How does that put a % sign on the end ?

              • 4. Re: ROUND function not working as expected
                Jonathan Drummey

                Hi Shin,

                 

                Unfortunately the Tableau-provided calculation that I referenced in that old post fails in some situations, for example with the number 1.16 and 2 decimal places where it returns 1.15 instead:

                 

                Screen Shot 2019-01-12 at 1.11.00 PM.png

                 

                I've been revisiting problem every once in awhile for years and think I finally nailed it. Note that the issue as I define it is, "For whatever reason we absolutely must return a string representation of a number with the proper formatting and are not able to use Tableau's built-in number formatting."

                 

                To solve this I created calculations that separate the integer & decimal portions and do the necessary math and then put them together into a rounded string (that also properly handles negative numbers and thousands separators):

                 

                Screen Shot 2019-01-12 at 1.24.16 PM.png

                 

                Besides being more accurate it should be faster in a number of cases because unlike the Tableau provided string calculation it doesn't have to do any string comparisons and cuts the # of string conversions from 5 to 2.

                 

                So people don't have to download the workbook here are the calcs, they all assume we're starting with a [Number] field.

                 

                Integer Portion:

                //take whole part of original number

                INT(ABS([Number]))

                //this math determines whether the whole number should be rounded or not

                + INT(

                    (

                        // get the number as a rounded integer

                        ROUND(ABS([Number])*POWER(10,[# of Decimal Places]),0)

                        //then substract the integer, we'll end up with 0/1, 0/10, 0/100 etc. as results

                        - INT(ABS([Number])) * POWER(10,[# of Decimal Places])

                    )

                    //then divide this by the multiple we've used to end up with 0 or 1

                    / POWER(10,[# of Decimal Places])

                )

                 

                Decimal Portion:

                //returns the decimal portion as a rounded integer number at

                //the desired number of decimal places

                (

                //do the rounding out at the desired number of decimal places

                ROUND(ABS([Number]) * POWER(10,[# of Decimal Places]), 0)

                //subtract the integer portion of the number

                    - INT(ABS([Number])) * POWER(10,[# of Decimal Places])

                )

                //if the result is divisible by 1, 10, 100, etc. then it needs to be 0

                //the modulo function handles that

                % POWER(10, [# of Decimal Places])

                 

                Rounded String:

                //this puts the integer & decimal parts back together and also handles a number of special situations:

                //  number is negative but rounds to 0

                //  Tableau's string conversion doesn't include thousands separators so we add them

                //  if the decimal portion doesn't have enough precision then pad out 0's as necessary

                 

                //negative sign

                IF [Number] < 0 AND NOT([Integer Portion] = 0 AND [Decimal Portion] = 0) THEN

                    '-'

                ELSE

                    ''

                END

                 

                //whole number portion

                //Uses thousand separators regex by Pooja Gandhi https://community.tableau.com/message/525702#525702

                //This does not work on table calcs as of v2018.3 https://community.tableau.com/ideas/6239,

                //Therefore if you are using table calculations you'll need to use a different method

                //for thousands separators: https://community.tableau.com/thread/134849

                + REGEXP_REPLACE(STR([Integer Portion]), '([0-9](?=(?:[0-9]{3})+(?![0-9])))', '$1,')

                 

                //decimal part

                + IF [# of Decimal Places] > 0 THEN

                    '.'

                    + STR([Decimal Portion])

                    //pad out 0's for # of decimal places

                    + IF INT(IFNULL(LOG([Decimal Portion]),0)) < ([# of Decimal Places] - 1) THEN

                        LEFT('000000000000000', [# of Decimal Places] - 1 - INT(IFNULL(LOG([Decimal Portion]),0)))

                    ELSE

                        ''

                    END

                ELSE

                    ''

                END

                 

                Tableau v2018.2 workbook is attached.

                 

                Jonathan

                3 of 3 people found this helpful
                • 5. Re: ROUND function not working as expected
                  Shinichiro Murakami

                  Thank you, Jonathan.

                   

                  Anyways, it's quite a bit complicated..

                  I am going to think about easier(?) one later..

                   

                  Thanks,

                  Shin

                  • 6. Re: ROUND function not working as expected
                    Jonathan Drummey

                    I agree, it is quite complicated and I’d love for someone find a simpler solution! What happened  for me as I was building out the calcs is that the simpler solutions only worked for certain numbers (or # of decimal points) and not others. I’d regularly have an “a-ha!” moment only to find out that the idea was of limited use or added more complexity than it was worth. Hopefully whoever else takes a look at this can find an easier solution.

                     

                    Also, to be clear about my requirements/design criteria I’d set myself the following goals: a) Minimizing the amount of string manipulation for performance reasons; b) Using as few conditional statements as possible because my experience has been that when using conditions in mathematical operations it’s all too easy to end up with a tangled mess, and that definitely happened here. For example I’m using absolute numbers and then converting back to negative at the very end to simplify the math;  c) Requiring that the solution would work on numbers of any size or precision, and d) Having a formula that would work at any calculation level. The solution that I posted fails d) as-is through using regex for the thousands separators where regex are not presently supported by table calcs, though in the formula I provided a link to a more complicated alternative using regular string functions and a lot of IF statements.

                     

                    Jonathan

                    1 of 1 people found this helpful
                    • 7. Re: ROUND function not working as expected
                      Anjali Sn

                      This is perfect as it solves my current problem but yeah very complicated. I will go with it for now. However, in your solution, is it possible to have consistent number of decimal places? For example, I have a number: 55.00 and I'm providing 2 as the number of decimal places, but when we get the rounded string for this number, it is coming as : 55.0. it's working fine for cases that have decimal parts for example 86.7512 gives 86.75.

                      • 8. Re: ROUND function not working as expected
                        Jonathan Drummey

                        Hi Anjali,

                         

                        I'm glad it's working for you (mostly) and I'd be glad to get it working for the number 55 or 55.00. However when I try that with the Excel spreadsheet that I'd posted in the workbook I see 55.00 as expected:

                         

                        Screen Shot 2019-01-14 at 7.20.46 PM.png

                         

                        Can you post a Tableau packaged workbook with sample data and the calculation showing the problem? That way I can diagnose what's going on.

                         

                        Jonathan

                        • 9. Re: ROUND function not working as expected
                          Jonathan Drummey

                          Anjali Sn, would you like help getting the consistent number of decimal places or are you ok with the solution as-is?

                           

                          Jonathan