stored values in tm1

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

stored values in tm1

Post by Analytics123 »

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 247 times
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: stored values in tm1

Post by Steve Rowe »

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.
Technical Director
www.infocat.co.uk
User avatar
qml
MVP
Posts: 1094
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: stored values in tm1

Post by qml »

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: 128
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 »

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: 1094
Joined: Mon Feb 01, 2010 1:01 pm
OLAP Product: TM1 / Planning Analytics
Version: 2.0.9 and all previous
Excel Version: 2007 - 2016
Location: London, UK, Europe

Re: stored values in tm1

Post by qml »

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: 1453
Joined: Wed May 28, 2008 9:09 am

Re: stored values in tm1

Post by David Usherwood »

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
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: stored values in tm1

Post by PavoGa »

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