incorrect consolidation value due to rounding at leaf level

[sfnicole]

Hi,
I have encountered some rounding issue at consolidation level. Fyi, I have a cube, let say cube A, the amount value that store in this cube are two decimal places (#,##0.00).

In Cube B, the format of amount value is "#,##0". When I put the data from cube A to cube B, the amount value will be auto formatted. However, the consolidation level in Cube B is based on the before formatted value. And, this cause the consolidation value does not tally with the total of the amount in child value. Please refer to the sample below:-

For eg.
Cube A
Prod1 100.63
Prod2 100.52
Total 201.15

Cube B
Prod1 101
Prod2 101
Total 201 (the correct amount should be 202)

Do you have any solution for it?

Thanks in advance.

[Michel Zijlema]

Hi,
I have encountered some rounding issue at consolidation level. Fyi, I have a cube, let say cube A, the amount value that store in this cube are two decimal places (#,##0.00).

In Cube B, the format of amount value is "#,##0". When I put the data from cube A to cube B, the amount value will be auto formatted. However, the consolidation level in Cube B is based on the before formatted value. And, this cause the consolidation value does not tally with the total of the amount in child value. Please refer to the sample below:-

For eg.
Cube A
Prod1 100.63
Prod2 100.52
Total 201.15

Cube B
Prod1 101
Prod2 101
Total 201 (the correct amount should be 202)

Do you have any solution for it?

Thanks in advance.

Formatting is a display functionality, it is not actually changing your numbers. If you want to round the numbers when copying data from source to destination, you can ue the RoundP rules (and TI) function.

Michel

[ajain86]

Where are you performing the rounding? If you have only formatted your view to not show decimals, then that does not change the underlying data values and would cause the situation you presented.

[Alan Kirk]

This is not a bug. Again, it's requested that people read the How To Make A Bug Post thread before posting in that forum. The post has been moved accordingly.

I have encountered some rounding issue at consolidation level.

No, you haven't. That's because you haven't been rounding anything, you've only been formatting it.

Fyi, I have a cube, let say cube A, the amount value that store in this cube are two decimal places (#,##0.00).

No, it doesn't. You've told it to display two decimal places. The values in the cube are stored as standard floating point values. The display precision that you specify only determines what the numbers look like, not what is stored.

In Cube B, the format of amount value is "#,##0". When I put the data from cube A to cube B, the amount value will be auto formatted. However, the consolidation level in Cube B is based on the before formatted value. And, this cause the consolidation value does not tally with the total of the amount in child value. Please refer to the sample below:-

For eg.
Cube A
Prod1 100.63
Prod2 100.52
Total 201.15

Cube B
Prod1 101
Prod2 101
Total 201 (the correct amount should be 202)

As you yourself said... the total value is 201.15, And that rounds to 201, not 202.

You will not get rounded numbers by simply formatting them. To get 202 in cube B (which, IMHO, would be the wrong value anyway) you would instead need to apply the Round or RoundP rules functions when you are pulling the N level numbers from cube A to cube B. Because as things stand you may think that you have 101 twice in cube B, but you don't. You have 100.63 formatted as 101, and 100.52 formatted as 101 And those are still adding to 201.15.

{Edit: Clearly I shouldn't have typed so much since two people beat me to it. But the answer still stands...}

[sfnicole]

Hi,
I have used round and roundp. It may resolved this issue but it also cause another issue. If the value is in negative, for eg. -101.5, the system will give -101 instead of -102. So, for this issue, do you have any solution for it?

Thanks.

[David Usherwood]

I believe (but could be challenged) that it is correct practice to round towards zero. if you don't like this, try something like
['Z'] = round(['Y']) + sign['Y'] -1;
(I'm sure someone will find a hole in the above logic, but hey...)