Calculation inconsistency after using a DB

Post Reply
jviegas@bi4all.pt
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

Post by jviegas@bi4all.pt »

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
ascheevel
Community Contributor
Posts: 216
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

Post by ascheevel »

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 user-input 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.
User avatar
Steve Rowe
Site Admin
Posts: 2291
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

Post by Steve Rowe »

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.
Technical Director
www.infocat.co.uk
jviegas@bi4all.pt
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

Post by jviegas@bi4all.pt »

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
Attachments
trace.jpg
trace.jpg (111.66 KiB) Viewed 360 times
parameters_Cube.jpg
parameters_Cube.jpg (19.83 KiB) Viewed 360 times
jviegas@bi4all.pt
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

Post by jviegas@bi4all.pt »

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
lotsaram
MVP
Posts: 3571
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

Post by lotsaram »

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 4E-12. 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.
Wim Gielis
MVP
Posts: 2912
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Calculation inconsistency after using a DB

Post by Wim Gielis »

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
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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis
jviegas@bi4all.pt
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

Post by jviegas@bi4all.pt »

Wim Gielis wrote: Tue Aug 02, 2022 8:09 am
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
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.
Hi Win and Lotsaram,

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
Wim Gielis
MVP
Posts: 2912
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Calculation inconsistency after using a DB

Post by Wim Gielis »

Hi Jorge,

It's Wim, not Win.

This is the "difference":
Capture.PNG
Capture.PNG (11.17 KiB) Viewed 305 times
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, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis
jviegas@bi4all.pt
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

Post by jviegas@bi4all.pt »

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.
Hi Wim (sorry on the previous typo error :) ),

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 pre-sales 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
Wim Gielis
MVP
Posts: 2912
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Calculation inconsistency after using a DB

Post by Wim Gielis »

Good ! You're welcome.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis
Duncan P
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

Post by Duncan P »

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.
tomok
MVP
Posts: 2823
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Calculation inconsistency after using a DB

Post by tomok »

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 accounting-related 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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
lotsaram
MVP
Posts: 3571
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

Post by lotsaram »

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 non-issue. 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.
Wim Gielis
MVP
Posts: 2912
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Calculation inconsistency after using a DB

Post by Wim Gielis »

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 non-issue. It's just how computers store and calculate decimals.
Indeed Lotsa.

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
Capture.PNG (25 KiB) Viewed 238 times
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis
jviegas@bi4all.pt
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

Post by jviegas@bi4all.pt »

Wim Gielis wrote: Tue Aug 02, 2022 3:02 pm
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 non-issue. It's just how computers store and calculate decimals.
Indeed Lotsa.

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
Hi Lotsa and Wim,

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 accounting-related 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
Wim Gielis
MVP
Posts: 2912
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Calculation inconsistency after using a DB

Post by Wim Gielis »

Can you please provide the real numbers that the users see, including the material difference.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis
Post Reply