企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 552|回复: 5

incorrect consolidation value due to rounding at lea ...

[复制链接]

70

主题

353

帖子

524

积分

高级会员

Rank: 4

积分
524
QQ
发表于 2014-3-17 15:38:19 | 显示全部楼层 |阅读模式
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.
回复

使用道具 举报

57

主题

378

帖子

507

积分

高级会员

Rank: 4

积分
507
发表于 2014-3-17 17:44:45 | 显示全部楼层
sfnicole wrote: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
回复 支持 反对

使用道具 举报

83

主题

416

帖子

588

积分

高级会员

Rank: 4

积分
588
QQ
发表于 2014-3-17 17:48:04 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

83

主题

418

帖子

603

积分

高级会员

Rank: 4

积分
603
QQ
发表于 2014-3-17 18:03:42 | 显示全部楼层
This is not a bug. Again, it's requested that people read the  thread before posting in that forum. The post has been moved accordingly.
sfnicole wrote: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.
sfnicole wrote: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.
sfnicole wrote: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...}

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

58

主题

371

帖子

514

积分

高级会员

Rank: 4

积分
514
发表于 2014-3-17 18:09:17 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

73

主题

406

帖子

585

积分

高级会员

Rank: 4

积分
585
发表于 2014-3-17 18:42:18 | 显示全部楼层
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...)
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2020-11-28 21:42 , Processed in 0.144605 second(s), 13 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表