Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikelees80
Contributor
Contributor

Create Cumulative Count of Records in script by date

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

   

AccountInstallDateSerial Number
1234515/07/201710
1234516/07/201711
1234519/07/201712
1234525//07/201713
1234529/07/201714
1234502/08/201715
1234505/08/201716
1234510/08/201717
1234512/08/201718

Example of Output

   

DateAccountCount
01/07/2017123450
02/07/2017123450
03/07/2017123450
04/07/2017123450
05/07/2017123450
06/07/2017123450
07/07/2017123450
08/07/2017123450
09/07/2017123450
10/07/2017123450
11/07/2017123450
12/07/2017123450
13/07/2017123450
14/07/2017123450
15/07/2017123451
16/07/2017123452
17/07/2017123452
18/07/2017123452
19/07/2017123453
20/07/2017123453
21/07/2017123453
22/07/2017123453
23/07/2017123453
24/07/2017123453
25/07/2017123454
26/07/2017123454
27/07/2017123454
28/07/2017123454
29/07/2017123455
30/07/2017123455
31/07/2017123455
01/08/2017123455
02/08/2017123456
03/08/2017123456
04/08/2017123456
05/08/2017123457
06/08/2017123457
07/08/2017123457
08/08/2017123457
09/08/2017123457
10/08/2017123458
11/08/2017123458
12/08/2017123459
13/08/2017123459

Any ideas on how to produce this would be great

1 Solution

Accepted Solutions
Kushal_Chawda

try this, seems to be working

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

1234, 10/8/2015, 18

];

T1:

LOAD date(MinDate+IterNo()-1) as InstallDate

While MinDate+IterNo()-1<=MaxDate;

LOAD monthstart(min(Date)) as MinDate,

          max(Date) as MaxDate;

LOAD FieldValue('InstallDate',RecNo()) as Date

AutoGenerate FieldValueCount('InstallDate');

Left Join(T1)

LOAD Distinct Account

Resident Data;

Left Join(T1)

LOAD *

Resident Data;

DROP Table Data;

Final:

LOAD *,

         if(Serial=0,if(Account=peek(Account),Peek('Cumulative'),Serial),Serial) as Cumulative;

LOAD *,

         autonumber(alt([Serial Number],'NA'),Account)-1 as Serial

Resident T1

Order by Account,InstallDate;

DROP Table T1;

View solution in original post

20 Replies
Anonymous
Not applicable

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
Master II
Master II

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
Not applicable

if different accounts should be separated?!?

then you should add a further condition like

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

mikelees80
Contributor
Contributor
Author

Yes I need to split the count by account

Anonymous
Not applicable

do you get it or do you need further help?

mikelees80
Contributor
Contributor
Author

I'm trying it now Robin

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

devarasu07
Master II
Master II

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
Contributor
Contributor
Author

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
Master III
Master III

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