Consolidation sum error - negative weighting

[CaptKirk]

Hello
I have searched forum but could not find any postings regarding this
Using PA2.0.6, we note the following behaviour

MEASURE: Net Commitment (C) = PC Amount (N) - PO Amount (N)
PC Amount is 122,663.31 (and when exported to Excel has 20 "0's) after
PO Amount is same

Net Commitment shows as 0.00000000014551915223
rather than 0.00

this is causing issues as we are listing where Net Commitment <> 0, and this scenario is displayed, when clearly it should not.

I know we can do a workaround such that we use an MDX to display where ABS(Net Commitment) >= 0.01 but that should not be needed.

one dimension element in cube view is consolidated (grouping of two N level cost centres, the rest of the dimensions are N level).
PC amount is against Cost centre 1, PO Amount is against CC 2, weighting of cost centre elements is = 1

the measures are formatted (attributes) to be category = Comma, Precision = 0

Any ideas ?

[Alan Kirk]

Your clue is right here:

PC Amount is 122,663.31 (and when exported to Excel has 20 "0's) after
PO Amount is same

Net Commitment shows as 0.00000000014551915223
rather than 0.00

You get this effect if you have tiny decimal amounts way out on the horizon.

If you're importing values, then round them to a small number of decimal places in the TI process before adding them to the cube.

If you're calculating them in rules, add rounding to the rules.

the measures are formatted (attributes) to be category = Comma, Precision = 0

Formatting is just that; formatting. It doesn't change the underlying cube values.

[CaptKirk]

Sorry Alan, I misled you.
When exported to excel it has 2 decimal places. When you change the format to increase decimal places, it is all 0's (just to prove it has nothing after the .31). Hence the value is $122,663.31.
the value for net commitment in the cube is the value I showed 0.0000000000001455 etc

[tomok]

PC Amount is 122,663.31
PO Amount is same

How do you know PO Amount is the same? Did you look at the unformatted value behind that cell in TM1? Take your formatting off the cube and compare those two amounts. They are probably not the same which is why the net is not equal to 0. This is where you follow the advice of Alan and make sure you round the values to two decimal places when loading them into the cube OR when you populate them with rule.

[CaptKirk]

Thankyou for your comments so far, but I do not believe they have hit the nail on the head.
Took formatting off, some screen shots attached.
I have since noted that in the Month dimension, I was using Whole of Life, which is made up of opening balance (N) but $0, Year (with Months as children), and a consolidation for Forward Years.
The issue is at the Forward Years consolidation level where the issue appears, but you will see, esp in last screen shott, that its children are $0.
Although the highlighted cell shows 0, it actually is not 0. it is 0.00000000005821... when exported to excel, but also as zero suppress is on, it should not show if it is actually 0

[CaptKirk]

also see attached

[Steve Rowe]

You probably need to read up on floating point arithmetic.

https://en.wikipedia.org/wiki/Floating-point_arithmetic

Basically this means that even if mathematically / logically something should net to 0 it sometimes won't due to the very small inaccuracies introduced by converting a decimal value to binary and back again.

So if you want your accuracy to be 2 d.p. then round any ruled values, but even this might cause a zero suppression failure occassionally.

I seem to remember there is cfg setting in this area but my googlefu is off the boil....

[CaptKirk]

Someone will make a motsa if they find a solution for that …..

[MGrain]

As Steve points out, there is a parameter in the TM1S.CFG file but it relates specifically to division:

MagnitudeDifferenceToBeZero