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

20 Replies
Kushal_Chawda

Another approach could be by using fastest way of calculating Min and max Date

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];

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 *,

         alt(if(AutoNumber([Serial Number],Account) >0,AutoNumber([Serial Number],Account) ,Peek('Cumulative')),0) as Cumulative

Resident T1

Order by Account,InstallDate;

DROP Table T1;

Kushal_Chawda

or even more simpler

Data:

LOAD *, AutoNumber([Serial Number],Account) as Serial 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];

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 *,

         alt(if(Serial>0,Serial,Peek('Cumulative')),0) as Cumulative

Resident T1

Order by Account,InstallDate;

DROP Table T1;

mikelees80
Contributor
Contributor
Author

Hi Krushal

This was very useful and I almost have this working, the issue I was when I add an additional Account the Cumulative starts at 1 instead 0

Any ideas

Kushal_Chawda

Hi Mike,

This could happen because let's say for Account B, you have serial number starts from installdate 1-08-2017 which is minimum date then your Cummulative will start with one as I have used autonumber. Do you want the it to start from 0?

mikelees80
Contributor
Contributor
Author

I need it to start at 0 for every account until it finds the first serial number ordered by date

Kushal_Chawda

try this

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];

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 *,

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

LOAD *,

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

Resident T1

Order by Account,InstallDate;

DROP Table T1;

mikelees80
Contributor
Contributor
Author

I'm still struggling

example account 1234 has a date 10/08/2015

when I select account 12345 years 2015 upto 15/07/2017 so Cumulative value of 1 however it needs to be 0

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, 5]
;



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 *,

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

LOAD *,

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

Resident T1

Order by Account,InstallDate;



DROP Table T1;

Anonymous
Not applicable

.. you asked for a script solution...

I think you need a frontend solution don't you?

mikelees80
Contributor
Contributor
Author

I then need to join this to another table to compare with sales, I tried to a front end solution using set analysis  but couldn't get that to work.

I spoke to a various people at my organization on how to do this.

Its driving m crazy

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;