TM1 Forum

Hi,

I am having some issues with zero out not happening for few rows , when analysed deeply ,

I have a cost sets cube where I load cost for material for specific time .

so I load 86.29 and the measure has format as comma with 2 decimals . But the number is shown as 86.29 .But when right clicked and trace calculation the number is stored as 86.290000000000006.

This value is being used bu another process to do some calculation .

Attached the screenshots which is causing some difference in 9th 0r 10 th decimal .attached image .



For quantity with 2 digits - 47 - it does - 47*86.29 -4228.21
For quantity with 3 digits - 298 - it does - 298 *86.290000000000000000006 = 29942.6300000000000000001

Can anyone help with this as this is causing a serious issue by not zeroing out due to decimal .

Thanks,

This issue comes up fairly often and is caused by the floating point arithmetic used by computers in general to store values, simply put not all decimal numbers can be stored exactly in binary leading to very small rounding issues.

https://en.wikipedia.org/wiki/Floating-point_arithmetic

In this manner X - X won't come out to be zero for some numbers.

Here is a good TM1-focused article on the subject with some suggestions on how to minimise the impact of the problem of number representation.

Should I use roundp while storing the values.

How are others overcoming this issue . do you always write roundp when there is a decimal value stored .

Analytics123 wrote: ↑Wed May 09, 2018 9:10 pm How are others overcoming this issue . do you always write roundp when there is a decimal value stored .

To be frank in most cases* this is not a problem at all. Number formatting usually takes care of the small discrepancies that you can generally only see on the fifteenth or sixteenth significant digit. If you need accuracy across more digits than that then you are probably using the wrong tool. But then again, all binary representations of decimal numbers have their quirks and deficiencies - that's just the way the universe works. As explained in the article, ROUNDP can only to a certain extent reduce inexactness, but cannot eliminate it because many decimal numbers simply cannot be exactly stored using binary representations like IEEE 754.

* It can be an issue when dealing with very large or very small numbers or division of one by the other.

I had an actuary client who didn't like the leftover rounding bits and bobs - so I just took the small change out of my pocket and offered 1 UK penny as full and final settlement for the discrepancy.

David Usherwood wrote: ↑Wed May 09, 2018 10:14 pm I had an actuary client who didn't like the leftover rounding bits and bobs - so I just took the small change out of my pocket and offered 1 UK penny as full and final settlement for the discrepancy.

LOL! I've done the same...