Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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
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
Hi,
RANGESUM ( SUM(Sales )ABOVE ( SUM( Sales ), 1, ROWNO() ) )
Regards
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;
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;
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
Please see the attached
Hi,
Load *,numsum(Sales+ peek([Cumm Sales])) as [Cumm Sales]
resident Table;