Rounding issue

[#1]

Hi ,

I have a expression which have overide the settings as Integer on Number tab.So it rounds off to the nearest integer for each as shown(Pls see the attached doc).But

does not round off the sum.Please help.

Thanks,

Swetha

[swuehl]

Hi Swetha,

I am sorry, I don't get your point with round off the sum, could you clarify what you do expect to see at what specific positions in your attached sample?

Stefan

[#3]

Hi Stefan,

In my attached exampleif you see the word doc the the expression SALES is rounded to nearset integer but at the summary level-21,768 its summing the expression before rounding off.If you total from spreadsheet it should be 21772 instead its showing 21678.Please let me know incase of further clarification.

Thanks,

Swetha

[swuehl]

I think I begin to understand.

You probably need to round the value in your expression instead of just setting the display format, so the sum will take the rounded values and not the raw data.

So try something like

=round( YOUREXPRESSION, 1)

as expression and this could just do the job.

[#5]

Hi Stefan,

Appreciated your help.Could resolve maximum of it except the below.Please let me know how to get this done.

I have changed my expression  as below:

=round(Sum({$<Store_fiscal=(P({<status_comp={Current}>})),status_comp={Current},

status={Current}>} if(Store_fiscal='0099' or  noncomp_fiscal='0099' , '0',

if(Coupon_Flag='Y' and (ACCT='MHOODC' or ACCT='QOP.C' or ACCT='MHOOD.') ,

aggr(round(sum(Sold_At*Qty*conv_rate),1),Order_Key)-Coupon_Dollars,

if(Coupon_Flag='Y' and (  ACCT<>'QOP.C' or ACCT<>'MHOODC' or ACCT<>'MHOOD.' ) ,

aggr(round(sum(Sold_At*Qty),1),Order_Key)-Coupon_Dollars,

  if(Coupon_Flag<>'Y' and (   ACCT='QOP.C' or ACCT='MHOODC' or ACCT='MHOOD.'),

(Sold_At*Qty*conv_rate),(Sold_At*Qty)

))))),1)

from :

Sum({$<Store_fiscal=(P({<status_comp={Current}>})),status_comp={Current},

status={Current}>} if(Store_fiscal='0099' or  noncomp_fiscal='0099' , '0',

if(Coupon_Flag='Y' and (ACCT='MHOODC' or ACCT='QOP.C' or ACCT='MHOOD.') ,

aggr(sum(Sold_At*Qty*conv_rate),Order_Key)-Coupon_Dollars,

if(Coupon_Flag='Y' and (  ACCT<>'QOP.C' or ACCT<>'MHOODC' or ACCT<>'MHOOD.' ) ,

aggr(sum(Sold_At*Qty),Order_Key)-Coupon_Dollars,

  if(Coupon_Flag<>'Y' and (   ACCT='QOP.C' or ACCT='MHOODC' or ACCT='MHOOD.'),

(Sold_At*Qty*conv_rate),

(Sold_At*Qty)))))).But the sum is not rounded.The result is as attached :date wise its rounding but at the summary level its not.

Thanks,

Swetha

[#6]

I have just changed my expression from :

Sum({$<Store_fiscal=(P({<status_comp={Current}>})),status_comp={Current},

status={Current}>} if(Store_fiscal='0099' or  noncomp_fiscal='0099' , '0',

if(Coupon_Flag='Y' and (ACCT='MHOODC' or ACCT='QOP.C' or ACCT='MHOOD.') ,

aggr(sum(Sold_At*Qty*conv_rate),Order_Key)-Coupon_Dollars,

if(Coupon_Flag='Y' and (  ACCT<>'QOP.C' or ACCT<>'MHOODC' or ACCT<>'MHOOD.' ) ,

aggr(sum(Sold_At*Qty),Order_Key)-Coupon_Dollars,

  if(Coupon_Flag<>'Y' and (   ACCT='QOP.C' or ACCT='MHOODC' or ACCT='MHOOD.'),

(Sold_At*Qty*conv_rate),

(Sold_At*Qty))))))      to

round(Sum({$<Store_fiscal=(P({<status_comp={Current}>})),status_comp={Current},

status={Current}>} if(Store_fiscal='0099' or  noncomp_fiscal='0099' , '0',

if(Coupon_Flag='Y' and (ACCT='MHOODC' or ACCT='QOP.C' or ACCT='MHOOD.') ,

aggr(sum(Sold_At*Qty*conv_rate),Order_Key)-Coupon_Dollars,

if(Coupon_Flag='Y' and (  ACCT<>'QOP.C' or ACCT<>'MHOODC' or ACCT<>'MHOOD.' ) ,

aggr(sum(Sold_At*Qty),Order_Key)-Coupon_Dollars,

  if(Coupon_Flag<>'Y' and (   ACCT='QOP.C' or ACCT='MHOODC' or ACCT='MHOOD.'),

(Sold_At*Qty*conv_rate),

(Sold_At*Qty)))))),1),still I get the totals rounded but by just each date nat the subtotal level.pls help.

[swuehl]

Yes, the expression is evaluated as total expression at summary level.

You would either need "sum of rows" total mode (this is available only in straight chart, on expression tab of chart properties), or the equivalent using advanced aggregation:

= sum(aggr( round( YOUREXPRESSION, 1), Date, Region))

I noticed that you used cyclic gorups for your dimension, this makes it probably a bit more complicated, but first check if you get the expected results with either sum of rows total mode or the advanced aggregation.

Besides, are you really sure that you want to aggregate the rounded Sales to total Sales like that? If you do it like a sum of rows with rounded values in each rows, your total Sales will almost surely be off the correct financial Sales sum, unless the rounding of the rows sum is part of your real business.

Hope this helps,

Stefan