1 2 Previous Next 25 Replies Latest reply on May 13, 2019 2:20 PM by Alexander Mou

# ROUND function returns strange results

Hi all,

I know there are a great many threads and a known bug around the ROUND() calculation function (especially when cast to a String), so I apologize if this is already covered elsewhere. I did search but did not find an exact match.

I'd like to understand why, in 8.2,  a Calculated Field like

ROUND( 11 /22)

or

ROUND (1 / 2)

or

ROUND ( 0,5 , 0 )

returns  3,815,694 as a result.

[In 9.0 Beta, it returns 227  .   ]

Even more baffling to me, a Calculated Field of

ROUND ( 0.5)   + 1

returns 7,631,388 .

---

Is this some sort of Floating Point error? (I am running Windows, 64-bit versions). Shouldn't ROUND of one half either be a zero or a one? Is this a bug or expected behavior? What workaround(s) exist(s) ?

• ###### 1. Re: ROUND function returns strange results

Are you able to provide a packaged workbook or screenshot of this error?

• ###### 2. Re: ROUND function returns strange results

You are using SUM() to aggregate. If you use MAX() or MIN() you will get the expected number.

--Shawn

• ###### 3. Re: ROUND function returns strange results

It's trivial to create in any workbook. Just make a calculated field that says ROUND (1 /2 )

Ken

• ###### 4. Re: ROUND function returns strange results

Well, not consciously.

But, to your point, I unchecked "Aggregate Measures" under Analysis and then got 3,815,694 copies of "1.00" on the screen after ~ 90 seconds.

So this has to do with the rendering pipeline I guess?

Originally I ran into this problem trying to troubleshoot someone else's workbook, where they have ROUND of a parameter divided by a data field. So I am not sure that an aggregate calculation like MIN() is gonna work. Will try it though.

• ###### 5. Re: ROUND function returns strange results

Posting your simple workbook would be helpful, so we're not guessing at why our calcs aren't returning the figures you are mentioning above.

Cheers,

--Shawn

• ###### 6. Re: ROUND function returns strange results

Understood and Agreed. The workbooks I have been working with contain company proprietary / confidential data, so I can't / won't upload one of those.

Here is is a similar example using SuperStore.  I  will post screen shots of my original workbook so that you know I am not BS'ing.

One key difference:  this Workbook that I have uploaded will show SUM( ) in the pill (If Aggregate Measures is checked), and to your point, if we change the aggregation from SUM to MIN then the result makes sense.

It's also interesting to me that this time we get yet another different number.

Thus, your diagnosis and repair is correct, but it still strikes me as strange behavior.

• ###### 7. Re: ROUND function returns strange results

I get it now.  The number I am getting is literally the Number of Records. Tableau is performing the ROUND (1/2) calculation once per row then aggregating the result. So if I changed it to ROUND (1/3) I'd get a zero but it'd be based on a sum of many zeroes, assuming I leave the default Aggregation as SUM.

Problem solved (or at least, understood), and thank you.

• ###### 8. Re: ROUND function returns strange results

Ken glad you got it worked out.

Cheers,

--Shawn

• ###### 9. Re: ROUND function returns strange results

round function is not rounding... what could be the reason? I can't share this workbook and I tried to create a new one mimicking the error but I couldn't replicate the error.

• ###### 10. Re: ROUND function returns strange results

Which version of 9.0 Desktop are you using?

• ###### 11. Re: ROUND function returns strange results

Proffesional Version 9.0

• ###### 12. Re: ROUND function returns strange results

Well I meant more at 9.0.1 ? 9.0.2 ? 9.0.3 ?

And what is the upstream data source?

• ###### 13. Re: ROUND function returns strange results

9.0.0(9000.15.0318.1720)64-bit with SQL Server 2012

• ###### 14. Re: ROUND function returns strange results

Okay so if it were me, I'd first upgrade to 9.0.3 just in case a relevant bug fix exists.

Then, I'd probably make a new Worksheet and try laying out your Bend to Bit DB field, and ROUND (Bend to Bit DB, 2 ) next to that, just to make sure it's ROUND that is not right rather than the STR conversion.

1 2 Previous Next