topic Re: cumulative total in QlikView

cumulative total

olivierrobin — Wed, 09 Mar 2016 14:32:50 GMT

hello,

i have a pivot table in which i want to calculate a cumulative total.

there are two dimensions : group and product

there are two columns : qty, whiwh is a quantity, and rank whiwh is the rank.

I want to compute the cumulative total of qty.

in the example given, there are 3 totals

1st value = 492128

2nd = 213630

3rd = 460939

the need is to have

1st = 492128

2nd = 492128+213630

3rd = 492128+213630+460939

as read in the community, i tried the above fucntion, bur it seems to work only at the detail level, not with dimensions

Re: cumulative total

Gysbert_Wassenaar — Wed, 09 Mar 2016 17:52:19 GMT

Try this expression:

if(Dimensionality()=1, sum(aggr(rangesum(above( sum(total <Groupe> QTECDE),0,RowNo())),Groupe)), sum(QTECDE))

Re: cumulative total

amayuresh — Thu, 10 Mar 2016 07:01:50 GMT

Thank you Gysbert below works

if(Dimensionality()=1, sum(aggr(rangesum(above( sum(total <Group> QTECDE),0,RowNo())),Group)), sum(QTECDE))

Please see my example below

my expression is

Sales=sum(SalesAmount)

Cummulatie Sale=

if(Dimensionality()=1, sum(aggr(rangesum(above(sum(total <Order_Year> SalesAmount),0,RowNo())),Order_Year)), sum(SalesAmount))

Re: cumulative total

olivierrobin — Thu, 10 Mar 2016 07:37:29 GMT

i already tried something like that.

with the code, the result is shown below.

the qtys seems to be multiplied by 3 for the 1st ctageory, by 2 for the 2ns, and by a lot for the 3rd

Re: cumulative total

Gysbert_Wassenaar — Thu, 10 Mar 2016 08:53:26 GMT

Post a small qlikview document that demonstrates the problem.

Re: cumulative total

olivierrobin — Thu, 10 Mar 2016 10:34:39 GMT

i forgot to explain that my 2 dimensions are computed if this way

so i can't use total <Groupe> in the formula

Re: cumulative total

amayuresh — Thu, 10 Mar 2016 10:47:00 GMT

What is your expression ?

Are you doing aggregation and subtotaling on Group dimension in expression like

if(Dimensionality()=1, sum(aggr(rangesum(above( sum(total <Group> QTECDE),0,RowNo())),Group)), sum(QTECDE)) ?

Re: cumulative total

olivierrobin — Thu, 10 Mar 2016 10:57:25 GMT

i tried

if(Dimensionality()=1, sum(aggr(rangesum(above( sum(total <Groupe> QTECDE),0,RowNo())),Groupe)), sum(QTECDE))

but it doesn't work as Groupe is a computed dimension (i think so)

did you see the application i posted ? (if not, please tell me the correct way to post it )

Re: cumulative total

Gysbert_Wassenaar — Thu, 10 Mar 2016 11:10:15 GMT

Then what you want cannot be done afaik. At least not with doing the calculations in the script and creating the necessary fields there.

Re: cumulative total

olivierrobin — Thu, 10 Mar 2016 12:18:46 GMT

here is the application

Re: cumulative total

olivierrobin — Thu, 10 Mar 2016 16:28:56 GMT

finally, the answer is :

if(Dimensionality()=1

, if(AGGR(IF(Rank(sum(QTECDE), 1)<=10, '1',IF(Rank(sum(QTECDE),

1)<=20,'2','3')), KEY) = 1

, sum({<KEY = {$(=Concat(AGGR(IF(Rank(sum(QTECDE), 1)<=10, chr(39) & KEY &

chr(39)), KEY), ','))}>} total QTECDE)

, if(AGGR(IF(Rank(sum(QTECDE), 1)<=10, '1',IF(Rank(sum(QTECDE),

1)<=20,'2','3')), KEY) = 2

, sum({<KEY = {$(=Concat(AGGR(IF(Rank(sum(QTECDE), 1)<=20, chr(39) & KEY &

chr(39)), KEY), ','))}>}total QTECDE)

, sum({<KEY = {$(=Concat(chr(39) & KEY & chr(39), ','))}>}total QTECDE)

)

), RangeSum(sum(QTECDE), above(total sum(QTECDE), 1, RowNo(total)))

)

and it works !!!!!!

thanks to Albert.Tran