topic Re: Re: Cumulative sum in QlikView

Cumulative sum

Fri, 06 Dec 2013 03:00:58 GMT

Hi all,

I have a table which is shown below :

Date	WorkingDate	Sales
1	1	100
2		10
3		3
4	2	300
5	3	500
6	4	900

I want to show the cumulative Sales by WorkingDate and the Sales of non-WorkingDate must be sumed to their nearest workingdate (the nearst workingdate must be greater than the non-workingdate). That's to say the sales of date 2 and date 3 will be sumed to workingdate 2.

The correct result table would like this :

Workingdate	CumulativeSales
1	100
2	413
3	913
4	1813

thanks.

Zhou

Re: Cumulative sum

jagan — Fri, 06 Dec 2013 03:06:41 GMT

HI,

Use Workingdate as Dimension in Straight table and Sum(CumulativeSales) as expression and select

Full Accumulation option in Expression tab of Chart Properties.

Hope this helps you.

Regards,

Jagan.

Re: Cumulative sum

Fri, 06 Dec 2013 03:14:25 GMT

Hi Jagan, Thanks for you prompt response. I did as you suggested while the result table is below: the non-workingdate's sales is been ignored.

Re: Cumulative sum

angelaecheverri — Fri, 06 Dec 2013 03:19:24 GMT

From the script you can do it..

Data1:

Load

Date,

WorkingDate,

Sales

From

Data.xls

Data2:

Load

Date as Date2,

WorkingDate as WorkingDate2,

Sales as Sales2,

Sales + peek(Sales) as SalesAcum

Resident Data1

Order by Date asc;

Re: Cumulative sum

angelaecheverri — Fri, 06 Dec 2013 03:20:58 GMT

If you need an specifc order like you load... could be...

Data1:

Load

Date,

WorkingDate,

Sales,

autonumber() as ID

From

Data.xls

Data2:

Load

Date as Date2,

WorkingDate as WorkingDate2,

Sales as Sales2,

Sales + peek(Sales) as SalesAcum,

Resident Data1

Order by ID asc;

Re: Re: Cumulative sum

jagan — Fri, 06 Dec 2013 03:28:20 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: Re: Cumulative sum

angelaecheverri — Fri, 06 Dec 2013 03:32:27 GMT

How can i Attach it i dont know how...

Re: Re: Cumulative sum

jagan — Fri, 06 Dec 2013 03:44:44 GMT

Hi,

Click on Use Advance Editor option on top of the reply text box.

Regards,

Jagan.

Re: Cumulative sum

Fri, 06 Dec 2013 08:26:46 GMT

Thank you Jagan, it works. But i choose to do it manually because of the complexity of my real data. Thanks.

Re: Cumulative sum

Fri, 06 Dec 2013 08:28:26 GMT

Thanks for your help. I have worked it out as Jagan suggested. Your solution is the same as his.