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,
stored values in tm1
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
stored values in tm1
- Attachments
-
- Decimal Issue.docx
- (53.45 KiB) Downloaded 249 times
- Steve Rowe
- Site Admin
- Posts: 2415
- 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
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.
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
www.infocat.co.uk
- 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
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
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Re: stored values in tm1
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 .
How are others overcoming this issue . do you always write roundp when there is a decimal value stored .
- 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
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.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 .
* It can be an issue when dealing with very large or very small numbers or division of one by the other.
Kamil Arendt
-
- Site Admin
- Posts: 1453
- Joined: Wed May 28, 2008 9:09 am
Re: stored values in tm1
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.
- PavoGa
- MVP
- Posts: 617
- 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
LOL! I've done the same...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.
Ty
Cleveland, TN
Cleveland, TN