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

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
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;