topic Re: Cumulative total in script in QlikView

Cumulative total in script

Tue, 28 Oct 2014 12:08:02 GMT

I am trying to get the cumulative sales by Booking Date per Service Month.

Please see below ...

Service Month	Booking Date	Sales	Cumulative Sales
Jul-14	2014-01-15	851	851
Jul-14	2014-02-01	100	951
Jul-14	2014-04-10	150	1101
Jul-14	2014-07-15	292	1393
Jul-14	2014-07-18	356	1749
Aug-14	2014-03-23	659	659
Aug-14	2014-08-23	784	1443
Aug-14	2014-08-25	254	1697
Aug-14	2014-08-28	169	1866
Sep-14	2014-04-19	32	32
Sep-14	2014-05-12	524	556
Sep-14	2014-05-28	94	650
Sep-14	2014-08-09	863	1513
Sep-14	2014-09-21	97	1610
Oct-14	2014-10-01	899	899
Oct-14	2014-10-13	966	1865
Oct-14	2014-10-16	444	2309

What i want to achieve is column 'Cumulative Sales'.

Any suggestions will be appreciated.

Regards,

Magen

Re: Cumulative total in script

ali_hijazi — Tue, 28 Oct 2014 12:14:42 GMT

in your script put

Load * , if(rowno()=1, sales, sales + peek(CumulativeSales) as CumulativeSales

resident table;

of course you need to sort the table by date first

Re: Cumulative total in script

sorrakis01 — Tue, 28 Oct 2014 12:16:58 GMT

Hi,

RANGESUM ( SUM(Sales )ABOVE ( SUM( Sales ), 1, ROWNO() ) )

Regards

Re: Cumulative total in script

anbu1984 — Tue, 28 Oct 2014 12:18:46 GMT

Initial:

Load ServiceMonth,Date#(BookingDate,'YYYY-MM-DD') As BookingDate,Sales Inline [

ServiceMonth,BookingDate,Sales

Jul-14,2014-01-15,851

Jul-14,2014-02-01,100

Jul-14,2014-04-10,150

Jul-14,2014-07-15,292

Jul-14,2014-07-18,356

Aug-14,2014-03-23,659

Aug-14,2014-08-23,784

Aug-14,2014-08-25,254

Aug-14,2014-08-28,169 ];

Join

Load ServiceMonth,BookingDate,If(ServiceMonth <> Previous(ServiceMonth),Sales,Sales+Peek(CumSum)) As CumSum Resident Initial Order by ServiceMonth,BookingDate;

Re: Cumulative total in script

simenkg — Tue, 28 Oct 2014 12:19:56 GMT

You want something like this.

First. Load the table from file.

Table1:
Load

[Service Month],

[Booking Date],

Sales,

0 as [Cumulative Sales]

from File1.qvd(qvd);

Then you load it resident. This is because you can not use the Order by clause on a load from file:

Noconcatenate

Table2:

load

[Service Month],

[Booking Date]

Sales,

if(Previous([Service Month]) = [Service Month], Previous([Cumulative Sales])+Sales,Sales) as [Cumulative Sales]

resident Table1

Order by [Service Month] asc, [Booking Date] asc;

drop table Table1;

Re: Cumulative total in script

ashfaq_haseeb — Tue, 28 Oct 2014 12:21:25 GMT

Like this.

Temp:
LOAD date(date#([Service Month],'MMM-YY')) as [Service Month], 
     [Booking Date], 
     Sales 
     //[Cumulative Sales]
FROM
[http://community.qlik.com/thread/139746]
(html, codepage is 1252, embedded labels, table is @1);


[Final]:  
LOAD  
  *,  
   IF([Service Month] = Previous([Service Month]), RangeSum([Sales], Peek('Cumulative Sales')),[Sales]) AS [Cumulative Sales] 
Resident Temp Order By [Service Month],[Booking Date] ASC;  
  
  
DROP Table Temp;

Regards

ASHFAQ

Re: Cumulative total in script

Kushal_Chawda — Tue, 28 Oct 2014 12:24:00 GMT

Please see the attached

Re: Cumulative total in script

Tue, 28 Oct 2014 12:26:14 GMT

Hi,

Load *,numsum(Sales+ peek([Cumm Sales])) as [Cumm Sales]

resident Table;