topic Rounding issue in QlikView

Rounding issue

Fri, 28 Oct 2011 16:07:11 GMT

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

Rounding issue

swuehl — Fri, 28 Oct 2011 16:29:36 GMT

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

Rounding issue

Fri, 28 Oct 2011 16:34:51 GMT

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

Rounding issue

swuehl — Fri, 28 Oct 2011 16:58:13 GMT

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.

Re: Rounding issue

Fri, 28 Oct 2011 18:02:47 GMT

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

Rounding issue

Fri, 28 Oct 2011 18:26:46 GMT

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.

Rounding issue

swuehl — Sun, 30 Oct 2011 12:09:03 GMT

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