ROUND Function Not Always Rounding

Highlighted
Contributor

Re: ROUND Function Not Always Rounding

I believe the issue may be because decimal fractions cannot be accurately stored in floating point binary.

 

This is a good explanation of the issue from a general technical standpoint:

https://docs.python.org/3/tutorial/floatingpoint.html

Chris Schulze
Solution Architect
Highlighted
Contributor

Re: ROUND Function Not Always Rounding

We're pleased to announce that we've introduced a new mode for the ROUND function in this weekend's release that addresses the problem you're seeing.

 

You can view the updated Anapedia content for ROUND here: https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/ROUND.html

 

And we have some guidance notes for customers on the use of the new 'EXACT' mode of the ROUND function: 

https://community.anaplan.com/t5/Releases/What-s-new-November-23-2019/ba-p/54370

 

In essence - nothing in your existing models will change, unless you choose to opt-in to using the new ROUND mode by adding the new EXACT keyword. 

 

Here's the example from the first post in this thread, showing the new behaviour:

clipboard_image_0.png

 

Highlighted
Certified Master Anaplanner
Certified Master Anaplanner

Re: ROUND Function Not Always Rounding

But if you take it back to TEXT, it appears as the scientific format again.  Is there anyway to get it back to text but still formatted to limited decimal places?

 

jwu_1-1600273980987.png

 

jwu_0-1600273896638.png

 

Highlighted
Contributor

Re: ROUND Function Not Always Rounding

Hi - thanks for your feedback. The behaviour you've observed is a result of the way that the TEXT function works, and it depends on the magnitude of the input value, as follows:

 

  • For input values smaller than 1e-3, TEXT will always return a result in scientific format
  • For input values between 1e-3 and 1e7, TEXT will return the decimal presentation of the input value
  • For input values larger than 1e7, TEXT will return a decimal presentation for integer inputs, and output in scientific format for non-integer input.

So at present, the only way to guarantee that you'll see a decimal representation (rather than the scientific format) for large input values is to ensure that they're rounded to the nearest integer.