topic Re: Re: Pivot table with running sum (2) in QlikView

Pivot table with running sum (2)

lucarizziero — Tue, 01 Oct 2013 08:29:07 GMT

I have the following Pivot Table

product	colour	date	cust. Order	running sum
141MBACAL0094	CR31	01/02/14	45	45
141MBACAL0094	CR31	02/02/14	19	64
141MBACAL0094	CR31	01/03/14	3	67
141MBACAL0094	OL71	01/02/14	52	52
141MBACAL0094	OL71	02/02/14	26	78

I'm not able to obtain the column named [running sum]: it should contains the running sum of the cust. Order column restarting each change of the combination [product]+[colour] dimensions.

In the solutions posted I can obtain a continuous running sum, while I need it restart each time the dimensions [product]+[colour] changes.

Thanks

Re: Pivot table with running sum (2)

tresB — Tue, 01 Oct 2013 08:39:41 GMT

Try like:

rangesum (above(total [cust. Order],1,2)))

Note: It's working would depend on your dimension occurance. If you find this not working, please share a sample app.

Re: Pivot table with running sum (2)

nizamsha — Tue, 01 Oct 2013 08:40:36 GMT

RangeSum(Above(TOTAL SUM(S),0,1000))

Re: Pivot table with running sum (2)

nizamsha — Tue, 01 Oct 2013 08:43:20 GMT

RangeSum(Above(TOTAL SUM([cust. Order]),0,1000))

Re: Re: Pivot table with running sum (2)

lucarizziero — Tue, 01 Oct 2013 09:18:40 GMT

here is a sample app

thanks

Luca

Re: Re: Re: Pivot table with running sum (2)

qlikpahadi07 — Tue, 01 Oct 2013 11:21:11 GMT

try this:

Aggr(RangeSum(Above(SUM(cust_order),0,RowNo())),colour, k)

check the attachment

Re: Pivot table with running sum (2)

tresB — Tue, 01 Oct 2013 11:30:04 GMT

In that case you can use the expression without TOTAL like:

RANGESUM(Column(1),ABOVE( Column(2)))

But then your result will change, the running sum would start over again for each new member of second last dimension value (here date). Unfortunately, TOTAL can't be used to control the dimension level. I.e, if you use it it will disregard all dimensions but not till a specific level.