TM1 Forum

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

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.

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.

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

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

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.

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.

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

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.

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

Good ! You're welcome.

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.

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.

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.

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.

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

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