stored values in tm1

Post Reply
Analytics123
Posts: 127
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

stored values in tm1

Post by Analytics123 » Wed May 09, 2018 7:33 pm

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,
Attachments
Decimal Issue.docx
(53.45 KiB) Downloaded 9 times

User avatar
Steve Rowe
Site Admin
Posts: 1754
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: stored values in tm1

Post by Steve Rowe » Wed May 09, 2018 8:30 pm

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.

User avatar
qml
MVP
Posts: 1054
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: stored values in tm1

Post by qml » Wed May 09, 2018 8:55 pm

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.
Kamil Arendt

Analytics123
Posts: 127
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: stored values in tm1

Post by Analytics123 » Wed May 09, 2018 9:10 pm

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 .

User avatar
qml
MVP
Posts: 1054
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: stored values in tm1

Post by qml » Wed May 09, 2018 9:45 pm

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.
Kamil Arendt

David Usherwood
Site Admin
Posts: 1318
Joined: Wed May 28, 2008 9:09 am

Re: stored values in tm1

Post by David Usherwood » 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.

User avatar
PavoGa
Community Contributor
Posts: 185
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 fixpack 7
Excel Version: 2013
Location: Cleveland, Tennessee

Re: stored values in tm1

Post by PavoGa » Thu May 10, 2018 12:12 pm

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...
Ty
Cleveland, TN

Post Reply