topic Re: Help with cumulative summary in QlikView

Help with cumulative summary

Mon, 08 Apr 2013 22:40:39 GMT

Hi all,

I need some help with the cumulative summary.

Here is the how the data is

Date	Count
03/30/2013	5
03/31/2013	7
04/01/2013	2
04/02/2013	8

Here is how the end result should look like. MOST IMPORTANTLY in the running totals should be the same even if the sort order of dates is changed (It is a requirement)

With ascending sort:

Date	Cumulative
03/30/2013	5
03/31/2013	12
04/01/2013	14
04/02/2013	22

With descending sort:

Date	Cumulative
04/02/2013	22
04/01/2013	14
03/31/2013	12
03/30/2013	5

I have tried with the Range sum RangeSum(Above( Sum(Count)),0, RowNo(TOTAL))) but SWITCHED to the option of creating an aggregated resident table where I am calculating the regular count of transactions and then joining it to the data model.

I need help with acheving the cumulative sum in the script where the data is loaded in the above mentioned resident table format. I know how to do in SQL. I would do a self join of the data table (D1 & D2) with with a condition on date (D1.Date <= D2.Date).

Can someone help me with how to do a self join to achieve the cumulative functionality in QLikview? Thanks in advance.

Re: Help with cumulative summary

Gysbert_Wassenaar — Tue, 09 Apr 2013 18:31:44 GMT

Load the dates and counts in a table then calculate the cumulative using the peek function:

Result:

load Date, Count, rangesum(Count,peek(Cumulative)) as Cumulative

resident SomeTable

order by Date;

Re: Help with cumulative summary

Tue, 09 Apr 2013 19:33:05 GMT

Thanks Gysbert for the response but it did not work. I would appreciate if you can correct where I am doing wrong in the sample QVW I attached.

Thanks

Sameer

Re: Help with cumulative summary

Gysbert_Wassenaar — Wed, 10 Apr 2013 11:38:55 GMT

See attached qvw.

Re: Help with cumulative summary

Wed, 10 Apr 2013 20:12:05 GMT

Gysbert - It worked like magic. Thanks a lot for your time.