
1. Re: ROUND function not working as expected
Deepak Rai Jan 11, 2019 6:09 PM (in response to Anjali Sn)STR(ROUND(SUM([column]),2))

2. Re: ROUND function not working as expected
Shinichiro Murakami Jan 11, 2019 10:27 PM (in response to Anjali Sn)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 DRUMMEY MAY 20, 2014 11:46 AM (IN RESPONSE TO JIM WAHL)
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:
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 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
1 of 1 people found this helpful

3. Re: ROUND function not working as expected
Chris McClellan Jan 11, 2019 11:54 PM (in response to Anjali Sn)How does that put a % sign on the end ?

4. Re: ROUND function not working as expected
Jonathan Drummey Jan 12, 2019 10:55 AM (in response to Anjali Sn)Hi Shin,
Unfortunately the Tableauprovided 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:
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 builtin 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):
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]), '([09](?=(?:[09]{3})+(?![09])))', '$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

strings and rounding.twbx 228.2 KB


5. Re: ROUND function not working as expected
Shinichiro Murakami Jan 12, 2019 11:05 AM (in response to Jonathan Drummey)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 Jan 13, 2019 8:58 AM (in response to Shinichiro Murakami)1 of 1 people found this helpfulI 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 “aha!” 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) asis 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

7. Re: ROUND function not working as expected
Anjali Sn Jan 14, 2019 1:33 PM (in response to Jonathan Drummey)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 Jan 14, 2019 4:22 PM (in response to Anjali Sn)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:
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 Jan 28, 2019 9:06 AM (in response to Anjali Sn)Anjali Sn, would you like help getting the consistent number of decimal places or are you ok with the solution asis?
Jonathan