Create Cumulative Count of Records in script by date

[mikelees80]

So I've been asked to provide cumulative count of our products we have installed by date. I need to to make sure we include date even if we haven't installed anything on that that. I already have a calendar QVD I can leverage.

This needs to be a script because I need to then link this new table to a different data set

Example of installed list

Account	InstallDate	Serial Number
12345	15/07/2017	10
12345	16/07/2017	11
12345	19/07/2017	12
12345	25//07/2017	13
12345	29/07/2017	14
12345	02/08/2017	15
12345	05/08/2017	16
12345	10/08/2017	17
12345	12/08/2017	18

Example of Output

Date	Account	Count
01/07/2017	12345	0
02/07/2017	12345	0
03/07/2017	12345	0
04/07/2017	12345	0
05/07/2017	12345	0
06/07/2017	12345	0
07/07/2017	12345	0
08/07/2017	12345	0
09/07/2017	12345	0
10/07/2017	12345	0
11/07/2017	12345	0
12/07/2017	12345	0
13/07/2017	12345	0
14/07/2017	12345	0
15/07/2017	12345	1
16/07/2017	12345	2
17/07/2017	12345	2
18/07/2017	12345	2
19/07/2017	12345	3
20/07/2017	12345	3
21/07/2017	12345	3
22/07/2017	12345	3
23/07/2017	12345	3
24/07/2017	12345	3
25/07/2017	12345	4
26/07/2017	12345	4
27/07/2017	12345	4
28/07/2017	12345	4
29/07/2017	12345	5
30/07/2017	12345	5
31/07/2017	12345	5
01/08/2017	12345	5
02/08/2017	12345	6
03/08/2017	12345	6
04/08/2017	12345	6
05/08/2017	12345	7
06/08/2017	12345	7
07/08/2017	12345	7
08/08/2017	12345	7
09/08/2017	12345	7
10/08/2017	12345	8
11/08/2017	12345	8
12/08/2017	12345	9
13/08/2017	12345	9

Any ideas on how to produce this would be great

[Anonymous]

1) count your installed list, I guess grouped InstallDate like that:

TEMP:

load

count(InstallDate) as Count

Installdate

resident YOURTABLE

group by InstallDate;

2) do a left join : YOURCALENDAR.qvd left join TEMP via Keyfield InstallDate

3) sum up everything

FINAL:

InstallDate,

Rangesum(Peek('CumulativeCount'), Count) as CumulativeCount

resident YOURJOINEDTABLE;

[devarasu07]

Hi,

Can you try like below?

Data:

LOAD * INLINE [

Account, InstallDate, Serial_Number

    12345, 15/07/2017, 10

    12345, 16/07/2017, 11

    12345, 19/07/2017, 12

    12345, 25//07/2017, 13

    12345, 29/07/2017, 14

    12345, 02/08/2017, 15

    12345, 05/08/2017, 16

    12345, 10/08/2017, 17

    12345, 12/08/2017, 18

];

Fact:

LOAD

    Account,

    InstallDate,

    Count(Serial_Number) + If(IsNull(Peek('Cumulative')), 0, Peek('Cumulative')) AS Cumulative

Resident Data

GROUP BY Account, InstallDate;

DROP TABLE Data;

Thanks,Deva

[Anonymous]

if different accounts should be separated?!?

then you should add a further condition like

if (account <> previous(acount) ... ) ...

[mikelees80]

Yes I need to split the count by account

[Anonymous]

do you get it or do you need further help?

[mikelees80]

I'm trying it now Robin

Thanks for the advice and i'll let you know how I get on

[devarasu07]

Hi,

Try like this,

Data:

LOAD InstallDate as Date,* INLINE [

Account, InstallDate, Serial_Number

    12345, 15/07/2017, 10

    12345, 16/07/2017, 11

    12345, 19/07/2017, 12

    12345, 25//07/2017, 13

    12345, 29/07/2017, 14

    12345, 02/08/2017, 15

    12345, 05/08/2017, 16

    12345, 10/08/2017, 17

    12345, 12/08/2017, 18

    12340, 10/08/2017, 37

    12340, 12/08/2017, 68

];

Fact:

LOAD

    Account,

    Date,

     If(Account = Previous(Account),Count(Serial_Number) + If(IsNull(Peek('Cumulative')), 0, Peek('Cumulative'))) AS Cumulative

Resident Data

GROUP BY Account, Date;

DROP TABLE Data;

[mikelees80]

Thanks for this solution however I need a Cumulative count for every day even if there aren't any install dates

ie 11/08/17

How would your code cope this this

[antoniotiman]

Hi Mike,

TableA:
LOAD * Inline [
Account, InstallDate, Serial Number
12345, 15/07/2017, 10
12345, 16/07/2017, 11
12345, 19/07/2017, 12
12345, 25/07/2017, 13
12345, 29/07/2017, 14
12345, 02/08/2017, 15
12345, 05/08/2017, 16
12345, 10/08/2017, 17
12345, 12/08/2017, 18];
LOAD Min(InstallDate) as MinDate,Max(InstallDate) as MaxDate
Resident TableA;
LET vMinDate = Peek('MinDate',RecNo());
LET vMaxDate = Peek('MaxDate',RecNo());
TableB:
LOAD DISTINCT Account,Date(MonthStart($(vMinDate))+IterNo()-1) as InstallDate
Resident TableA
While Date(MonthStart($(vMinDate))+IterNo()-1) <= $(vMaxDate);
Left Join (TableB) LOAD * Resident TableA;
LOAD *,If(Account=Peek(Account) and Len(Trim([Serial Number])) > 0,
RangeSum(Peek(Cumulative),1),RangeSum(Peek(Cumulative),0)) as Cumulative
Resident TableB Order By Account,InstallDate;
Drop Table TableA,TableB;

Regards,

Antonio