topic Re: can not subtotal when use aggr function in QlikView

can not subtotal when use aggr function

Wed, 26 Mar 2014 12:01:11 GMT

Hi all,

Please see below image, Total column calculate 0 (ZERO) against some branches. Which is wrong, it suppose to give me total.

My Expression is below

if(vFromDate >= Sdate1,sum({<T_Date= {'>=$(vFromDate) <=$(vToDate)'} >}Tday),
aggr(sum(if(T_Date>=Sdate1 and T_Date <= vToDate ,Tday)), DECDDT1,Sdate1, Branch, [Branch Name],[RSM Name]))

Is anything wrong/modify in expression?

Re: can not subtotal when use aggr function

michael_maeuser — Wed, 26 Mar 2014 12:03:24 GMT

I guess you neet to put the whole expression in an additional sum

if(vFromDate >= Sdate1,sum({<T_Date= {'>=$(vFromDate) <=$(vToDate)'} >}Tday),
SUM(aggr(sum(if(T_Date>=Sdate1 and T_Date <= vToDate ,Tday)), DECDDT1,Sdate1, Branch, [Branch Name],[RSM Name])))

Re: can not subtotal when use aggr function

Wed, 26 Mar 2014 12:07:09 GMT

I have done this but now total is coming up complete wrong.

see below

Re: can not subtotal when use aggr function

michael_maeuser — Wed, 26 Mar 2014 12:09:59 GMT

as far as I see the total column shows the sum of the last three columns. what do you expect as result?

Re: can not subtotal when use aggr function

michael_maeuser — Wed, 26 Mar 2014 12:13:14 GMT

ok now I see your problem.

you can try this

f(vFromDate >= Sdate1,sum({<T_Date= {'>=$(vFromDate) <=$(vToDate)'} >}Tday),
SUM(distinct aggr(sum(if(T_Date>=Sdate1 and T_Date <= vToDate ,Tday)), DECDDT1,Sdate1, Branch, [Branch Name],[RSM Name])))

or you can do it with dimensionality / secondary dimensionality function

Re: can not subtotal when use aggr function

Wed, 26 Mar 2014 12:17:28 GMT

What ever that data by Month is correct but total is wrong for example...

Branch no 180 total should be 46(19+22+5) and so on for other branches.

Re: can not subtotal when use aggr function

michael_maeuser — Wed, 26 Mar 2014 12:22:28 GMT

can you upload sample data?

Re: can not subtotal when use aggr function

yevgeniy — Wed, 26 Mar 2014 12:40:41 GMT

Try to add to start the expression sum() :

sum(

if(vFromDate >= Sdate1,sum({<T_Date= {'>=$(vFromDate) <=$(vToDate)'} >}Tday),
aggr(sum(if(T_Date>=Sdate1 and T_Date <= vToDate ,Tday)), DECDDT1,Sdate1, Branch, [Branch Name],[RSM Name]))

)

Re: can not subtotal when use aggr function

Wed, 26 Mar 2014 12:44:43 GMT

Now working. No data comes up.

Re: can not subtotal when use aggr function

Wed, 26 Mar 2014 12:48:13 GMT

I can't provide sample data because its linked with other 10 tables.

I have notice that data is correct by month but total is wrong only when if T_Date>=Sdate1.

Vinay

Re: can not subtotal when use aggr function

Wed, 26 Mar 2014 14:40:19 GMT

Please Anybody Can Help!!!

Vinay

Re: can not subtotal when use aggr function

yevgeniy — Wed, 26 Mar 2014 15:53:05 GMT

aggr(

if(vFromDate >= Sdate1

,sum({<T_Date= {'>=$(vFromDate) <=$(vToDate)'} >}Tday),
sum(if(T_Date>=Sdate1 and T_Date <= vToDate ,Tday)),

DECDDT1,Sdate1, Branch, [Branch Name],[RSM Name])