Calculation inconsistency after using a DB

 Posts: 67
 Joined: Fri Oct 14, 2011 3:15 pm
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 13
 Location: Portugal
Calculation inconsistency after using a DB
Hi all,
Couldn't find anything about this topic but would like to ask something about decimals in TM1 (using architect and tm1web).
Have a client reporting that after placing an assumption measure as 4.77 (in a specific cube for parameters) when checking another cube that uses this information they have differences.
When I traced the calculation I've noticed that the measure data from the DB is 4.769999999996.
I've tried to round the number (Both in target or adding a calculated measure in the source to round to 2 decimals) and even like that the cube uses the 4.769999999996 instead of the 4.77.
(it's a float issue but I can't find a way to solve this)
Does anyone have a clue how to get over this one ?
Thanks.
Kind Regards,
Jorge
Couldn't find anything about this topic but would like to ask something about decimals in TM1 (using architect and tm1web).
Have a client reporting that after placing an assumption measure as 4.77 (in a specific cube for parameters) when checking another cube that uses this information they have differences.
When I traced the calculation I've noticed that the measure data from the DB is 4.769999999996.
I've tried to round the number (Both in target or adding a calculated measure in the source to round to 2 decimals) and even like that the cube uses the 4.769999999996 instead of the 4.77.
(it's a float issue but I can't find a way to solve this)
Does anyone have a clue how to get over this one ?
Thanks.
Kind Regards,
Jorge

 Community Contributor
 Posts: 223
 Joined: Fri Feb 15, 2013 5:49 pm
 OLAP Product: TM1
 Version: PA 2.0.9.1
 Excel Version: 365
 Location: Minneapolis, USA
Re: Calculation inconsistency after using a DB
What is the value when you copy the unformatted value of the input cell? If the user says they inputted 4.77 and you're seeing something different, isn't this simply userinput error?
Is the end user inputting at a zero level or a consolidation using TM1Web/PAX/PAW? If at a consolidation, it would kick off a proportional spread operation which could get you some longtail decimals like you're seeing.
Is the end user inputting at a zero level or a consolidation using TM1Web/PAX/PAW? If at a consolidation, it would kick off a proportional spread operation which could get you some longtail decimals like you're seeing.
 Steve Rowe
 Site Admin
 Posts: 2297
 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: Calculation inconsistency after using a DB
Hi Jorge,
I've never found a way around these kind of issues when it gets down to a fundamental issue between floating point arithmatic and decimal.
It depends on the downstream calculation but rather than creating a calculated measure that was rounded and so still trying and failing to store 4.77 exactly, did you try putting round around the measure in the calcuation?
Obviously this won't help for a simple consolidation.
Is it producing a material difference or just a small one versus an Excel model? Excel suffers from this is just as much as TM1, I'd just put a round on any variance clautions in Excel vs TM1.
I've never found a way around these kind of issues when it gets down to a fundamental issue between floating point arithmatic and decimal.
It depends on the downstream calculation but rather than creating a calculated measure that was rounded and so still trying and failing to store 4.77 exactly, did you try putting round around the measure in the calcuation?
Obviously this won't help for a simple consolidation.
Is it producing a material difference or just a small one versus an Excel model? Excel suffers from this is just as much as TM1, I'd just put a round on any variance clautions in Excel vs TM1.
Technical Director
www.infocat.co.uk
www.infocat.co.uk

 Posts: 67
 Joined: Fri Oct 14, 2011 3:15 pm
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 13
 Location: Portugal
Re: Calculation inconsistency after using a DB
Hi Ascheevel,
Thanks for the reply.
I'm actually able to mimic the behavior.
Input is being done manually at a leaf level in a cube as 4.77
When I simply do a DB on a different cube (also at leaf level) I get the 4.7666666669 (around this  almost 15 decimal cases). This remains even if I apply a roundp for 2 decimals. the DB is ignoring this and working with the full number.
I'm already taking the calc away and just concentrating in the DB as it's show the error already like that.
Parameters Cube:
check image attached : parameters_Cube
Trace CAlculation in destination cube:
trace image
Note that the calculation here is complete but already tested only with the DB and the inconsistency remains
I believe it is a known issue with the float but I'm just wondering if someone already got through this.
Kind Regards,
Jorge
Thanks for the reply.
I'm actually able to mimic the behavior.
Input is being done manually at a leaf level in a cube as 4.77
When I simply do a DB on a different cube (also at leaf level) I get the 4.7666666669 (around this  almost 15 decimal cases). This remains even if I apply a roundp for 2 decimals. the DB is ignoring this and working with the full number.
I'm already taking the calc away and just concentrating in the DB as it's show the error already like that.
Parameters Cube:
check image attached : parameters_Cube
Trace CAlculation in destination cube:
trace image
Note that the calculation here is complete but already tested only with the DB and the inconsistency remains
I believe it is a known issue with the float but I'm just wondering if someone already got through this.
Kind Regards,
Jorge
 Attachments

 trace.jpg (111.66 KiB) Viewed 830 times

 parameters_Cube.jpg (19.83 KiB) Viewed 830 times

 Posts: 67
 Joined: Fri Oct 14, 2011 3:15 pm
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 13
 Location: Portugal
Re: Calculation inconsistency after using a DB
Hi Steve,
Already tried to round in the calculation as also (even if it would be stupid) to round to a different measure in the source cube (rounding the input data) and using this new measure. For both it failed.
the differences are detected at employee/month/cost center level (meaning at leaf level already). If we consider around 50 Cost centers, around 1000ish employees and 12 months it might become material to them.
Kind Regards,
Jorge
Already tried to round in the calculation as also (even if it would be stupid) to round to a different measure in the source cube (rounding the input data) and using this new measure. For both it failed.
the differences are detected at employee/month/cost center level (meaning at leaf level already). If we consider around 50 Cost centers, around 1000ish employees and 12 months it might become material to them.
Kind Regards,
Jorge

 MVP
 Posts: 3572
 Joined: Fri Mar 13, 2009 11:14 am
 OLAP Product: TableManager1
 Version: PA 2.0.x
 Excel Version: Office 365
 Location: Switzerland
Re: Calculation inconsistency after using a DB
This is simply a floating point issue which doesn't need to be solved, or can be easily "solved" by simply adjusting the precision of the display to 2dp.
The difference between 4.77 and 4.769999999996 is at the 12th decimal place, 0.000000000004 or 4E12. That is the difference is effectively zero. It won't make a shred of difference to any calculations. The issue is more human perception that the eye sees that 4.77 and 4.769999999996 are different, but really they are not.
The difference between 4.77 and 4.769999999996 is at the 12th decimal place, 0.000000000004 or 4E12. That is the difference is effectively zero. It won't make a shred of difference to any calculations. The issue is more human perception that the eye sees that 4.77 and 4.769999999996 are different, but really they are not.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

 MVP
 Posts: 2932
 Joined: Mon Dec 29, 2008 6:26 pm
 OLAP Product: TM1, Jedox
 Version: PAL 2.0.9.14
 Excel Version: Microsoft 365
 Location: Brussels, Belgium
 Contact:
Re: Calculation inconsistency after using a DB
Following the post of Lotsaram, you will need a massive amount of cells and big numbers to end up with any meaningful or tangible difference.jviegas@bi4all.pt wrote: ↑Mon Aug 01, 2022 4:51 pm Hi Steve,
Already tried to round in the calculation as also (even if it would be stupid) to round to a different measure in the source cube (rounding the input data) and using this new measure. For both it failed.
the differences are detected at employee/month/cost center level (meaning at leaf level already). If we consider around 50 Cost centers, around 1000ish employees and 12 months it might become material to them.
Kind Regards,
Jorge
Best regards,
Wim Gielis
Excel Most Valuable Professional, 20112014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis
Wim Gielis
Excel Most Valuable Professional, 20112014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis

 Posts: 67
 Joined: Fri Oct 14, 2011 3:15 pm
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 13
 Location: Portugal
Re: Calculation inconsistency after using a DB
Hi Win and Lotsaram,Wim Gielis wrote: ↑Tue Aug 02, 2022 8:09 amFollowing the post of Lotsaram, you will need a massive amount of cells and big numbers to end up with any meaningful or tangible difference.jviegas@bi4all.pt wrote: ↑Mon Aug 01, 2022 4:51 pm Hi Steve,
Already tried to round in the calculation as also (even if it would be stupid) to round to a different measure in the source cube (rounding the input data) and using this new measure. For both it failed.
the differences are detected at employee/month/cost center level (meaning at leaf level already). If we consider around 50 Cost centers, around 1000ish employees and 12 months it might become material to them.
Kind Regards,
Jorge
My problem is that they are questioning the difference.
Imagine this scenario : 12 months * 1000 employees * 22 days * 4.77
Now do the same with 4.76666666669 (more or less like the number shown in the picture.
Just like that in this simple example you get near 800 as a difference. As they are comparing with their numbers going away from the excel files they start questioning the numbers.
And precision is true for the visual part but due to the multiplying it doesn't make a difference if you format to 2 decimals or not because you still have the difference.
Thanks both for the feedback and brainstorm over this one (I truly believe this will be an hard one to crack with the customer).
Kind regards,
Jorge

 MVP
 Posts: 2932
 Joined: Mon Dec 29, 2008 6:26 pm
 OLAP Product: TM1, Jedox
 Version: PAL 2.0.9.14
 Excel Version: Microsoft 365
 Location: Brussels, Belgium
 Contact:
Re: Calculation inconsistency after using a DB
Hi Jorge,
It's Wim, not Win.
This is the "difference":
I would not lose any sleep over this 0.07%.
I'm certainly not an expert on this.
Could it be the case that some numbers are slightly higher than the original number ? Where the effect would be the inverse and the total net effect almost zero (even less than the above) ?
Also, when the customer is validating, I guess they will use formulas to calculate the rounded percentage difference and anything that is too small will be deemed okay.
It's Wim, not Win.
This is the "difference":
I would not lose any sleep over this 0.07%.
I'm certainly not an expert on this.
Could it be the case that some numbers are slightly higher than the original number ? Where the effect would be the inverse and the total net effect almost zero (even less than the above) ?
Also, when the customer is validating, I guess they will use formulas to calculate the rounded percentage difference and anything that is too small will be deemed okay.
Best regards,
Wim Gielis
Excel Most Valuable Professional, 20112014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis
Wim Gielis
Excel Most Valuable Professional, 20112014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis

 Posts: 67
 Joined: Fri Oct 14, 2011 3:15 pm
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 13
 Location: Portugal
Re: Calculation inconsistency after using a DB
Hi Wim (sorry on the previous typo error ),Wim Gielis wrote: ↑Tue Aug 02, 2022 8:34 am Hi Jorge,
It's Wim, not Win.
This is the "difference":
Capture.PNG
I would not lose any sleep over this 0.07%.
I'm certainly not an expert on this.
Could it be the case that some numbers are slightly higher than the original number ? Where the effect would be the inverse and the total net effect almost zero (even less than the above) ?
Also, when the customer is validating, I guess they will use formulas to calculate the rounded percentage difference and anything that is too small will be deemed okay.
Going to try to talk with them about this.
There are a few parameters with this issue (some bigger some equal. They are reporting this basically due to the validation with excel where they get the 4.77 as input).
Also while talking with a person on IBM presales we tried to turn this around using roundp in different ways or checking if the number could be calculated otherwise but I'm feeling that this will need to be talked with the customer and check for a middle ground.
As they are replicating the reported forecast since Jan 2022 they intend to have the same numbers as the ones reported to the group (as such, the 0.07% might or might not be an issue. let's see if I can get to some agreement).
Thanks again, kind regards,
Jorge

 MVP
 Posts: 2932
 Joined: Mon Dec 29, 2008 6:26 pm
 OLAP Product: TM1, Jedox
 Version: PAL 2.0.9.14
 Excel Version: Microsoft 365
 Location: Brussels, Belgium
 Contact:
Re: Calculation inconsistency after using a DB
Good ! You're welcome.
Best regards,
Wim Gielis
Excel Most Valuable Professional, 20112014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis
Wim Gielis
Excel Most Valuable Professional, 20112014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis

 MVP
 Posts: 600
 Joined: Wed Aug 17, 2011 1:19 pm
 OLAP Product: TM1
 Version: 9.5.2 10.1 10.2
 Excel Version: 2003 2007
 Location: York, UK
Re: Calculation inconsistency after using a DB
To expand on lotsaram's point that they are the same this https://docs.python.org/3/tutorial/floatingpoint.html is the clearest explanation I have found of the problem. It is from the documentation for the Python programming language but what it says applies to all systems that use IEEE754 double precision binary floating point numbers to represent decimals. That includes TM1, Excel, Cognos Planning, and Anaplan. The reason that these systems all use IEEE754 to represent decimals is that mathematical operations on numbers represented in this way are performed in hardware on all modern processors and are orders of magnitude faster than on any decimal representation.

 MVP
 Posts: 2823
 Joined: Tue Feb 16, 2010 2:39 pm
 OLAP Product: TM1, Palo
 Version: Beginning of time thru 10.2
 Excel Version: 2003200720102013
 Location: Atlanta, GA
 Contact:
Re: Calculation inconsistency after using a DB
Jorge, the bottom line is this has been the behavior in TM1 since pretty much when the tool was first created. It was initially built for accountingrelated apps and this is not normally an issue for budgeting and planning. If you just explain to your users that this is a limitation in the tool, that you have no control over, I'm sure it will be okay. I have had to have that same conversation many times and it's always worked out.

 MVP
 Posts: 3572
 Joined: Fri Mar 13, 2009 11:14 am
 OLAP Product: TableManager1
 Version: PA 2.0.x
 Excel Version: Office 365
 Location: Switzerland
Re: Calculation inconsistency after using a DB
Hi Jorge,
I think at some point you hgave transposed some 9s to 6s.
Originally you say 4.769999999996 (but from the screenshot it is 4.7700000000999996). But then in a later post you quote 4.7666666669. This number can't be correct and if this is the case the difference (althoug as Wim says only 0.07%) is far too large to be due to floating point. There is something else going on, or you just made a typo.
If the real number is either of the first 2 then as stated already this is really a nonissue. It's just how computers store and calculate decimals.
I think at some point you hgave transposed some 9s to 6s.
Originally you say 4.769999999996 (but from the screenshot it is 4.7700000000999996). But then in a later post you quote 4.7666666669. This number can't be correct and if this is the case the difference (althoug as Wim says only 0.07%) is far too large to be due to floating point. There is something else going on, or you just made a typo.
If the real number is either of the first 2 then as stated already this is really a nonissue. It's just how computers store and calculate decimals.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

 MVP
 Posts: 2932
 Joined: Mon Dec 29, 2008 6:26 pm
 OLAP Product: TM1, Jedox
 Version: PAL 2.0.9.14
 Excel Version: Microsoft 365
 Location: Brussels, Belgium
 Contact:
Re: Calculation inconsistency after using a DB
Indeed Lotsa.lotsaram wrote: ↑Tue Aug 02, 2022 2:41 pm Hi Jorge,
I think at some point you hgave transposed some 9s to 6s.
Originally you say 4.769999999996 (but from the screenshot it is 4.7700000000999996). But then in a later post you quote 4.7666666669. This number can't be correct and if this is the case the difference (althoug as Wim says only 0.07%) is far too large to be due to floating point. There is something else going on, or you just made a typo.
If the real number is either of the first 2 then as stated already this is really a nonissue. It's just how computers store and calculate decimals.
In retrospect, I already had a feeling that the deviation was still way too big. Even if only 0.07%. But no time to look at it in detail so thanks for picking up the difference.
Updated figures with 2 situation for the 4.77. The people that have an issue with this, will probably have many many more issues in life
Or just way too much time ton their hands.
Best regards,
Wim Gielis
Excel Most Valuable Professional, 20112014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis
Wim Gielis
Excel Most Valuable Professional, 20112014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis

 Posts: 67
 Joined: Fri Oct 14, 2011 3:15 pm
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 13
 Location: Portugal
Re: Calculation inconsistency after using a DB
Hi Lotsa and Wim,Wim Gielis wrote: ↑Tue Aug 02, 2022 3:02 pmIndeed Lotsa.lotsaram wrote: ↑Tue Aug 02, 2022 2:41 pm Hi Jorge,
I think at some point you hgave transposed some 9s to 6s.
Originally you say 4.769999999996 (but from the screenshot it is 4.7700000000999996). But then in a later post you quote 4.7666666669. This number can't be correct and if this is the case the difference (althoug as Wim says only 0.07%) is far too large to be due to floating point. There is something else going on, or you just made a typo.
If the real number is either of the first 2 then as stated already this is really a nonissue. It's just how computers store and calculate decimals.
In retrospect, I already had a feeling that the deviation was still way too big. Even if only 0.07%. But no time to look at it in detail so thanks for picking up the difference.
Updated figures with 2 situation for the 4.77. The people that have an issue with this, will probably have many many more issues in life
Or just way too much time ton their hands.
Capture.PNG
1st numbers were examples to explain the issue and screenshots similar although using the parameter number (in this case 4.77).
I understand the float issue, even more if due to a calc. But in this case it's an value manually added by the user.
I will raise a ticket although knowing that IBM answer might be the same. But we agree that if a person adds 4.77 and the value is transfered to a different cube (reinforcing that no calculations are done) than the value should be the same.
Don't get me wrong, it's not saying something bad about PA but this is a real problem in this case.
Regarding the statement for the accountingrelated apps that's here the problem gets worse. the Controllers are loading historical data and now facing the differences.
Imagine if I would be loading actual into an Actuals Cube by GL account and use DB to place them in a P&L Cube. This behavior will get me into trouble just for that.
Don't get me wrong, really appreciate for the brainstorming and your time on this
Kind Regards,
Jorge

 MVP
 Posts: 2932
 Joined: Mon Dec 29, 2008 6:26 pm
 OLAP Product: TM1, Jedox
 Version: PAL 2.0.9.14
 Excel Version: Microsoft 365
 Location: Brussels, Belgium
 Contact:
Re: Calculation inconsistency after using a DB
Can you please provide the real numbers that the users see, including the material difference.
Best regards,
Wim Gielis
Excel Most Valuable Professional, 20112014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis
Wim Gielis
Excel Most Valuable Professional, 20112014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly https://github.com/wimgielis