Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Cumulative total in script

Hi

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

Please see below ...

Service MonthBooking DateSalesCumulative Sales
Jul-142014-01-15851851
Jul-142014-02-01100951
Jul-142014-04-101501101
Jul-142014-07-152921393
Jul-142014-07-183561749
Aug-142014-03-23659659
Aug-142014-08-237841443
Aug-142014-08-252541697
Aug-142014-08-281691866
Sep-142014-04-193232
Sep-142014-05-12524556
Sep-142014-05-2894650
Sep-142014-08-098631513
Sep-142014-09-21971610
Oct-142014-10-01899899
Oct-142014-10-139661865
Oct-142014-10-164442309

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

Any suggestions will be appreciated.

Regards,

Magen

7 Replies
ali_hijazi
Partner - Master II
Partner - Master II

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

I can walk on water when it freezes
sorrakis01
Specialist
Specialist

Hi,

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

Regards

anbu1984
Master III
Master III

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;

simenkg
Specialist
Specialist

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;

ashfaq_haseeb
Champion III
Champion III

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

Kushal_Chawda

Please see the attached

Not applicable
Author

Hi,

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

resident Table;