topic Create Cumulative Count of Records in script by date in QlikView

Create Cumulative Count of Records in script by date

mikelees80 — Mon, 14 Aug 2017 09:05:35 GMT

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

Re: Create Cumulative Count of Records in script by date

Anonymous — Mon, 14 Aug 2017 09:19:04 GMT

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;

Re: Create Cumulative Count of Records in script by date

devarasu07 — Mon, 14 Aug 2017 09:19:59 GMT

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

Re: Create Cumulative Count of Records in script by date

Anonymous — Mon, 14 Aug 2017 09:27:43 GMT

if different accounts should be separated?!?

then you should add a further condition like

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

Re: Create Cumulative Count of Records in script by date

mikelees80 — Mon, 14 Aug 2017 09:30:14 GMT

Yes I need to split the count by account

Re: Create Cumulative Count of Records in script by date

Anonymous — Mon, 14 Aug 2017 09:46:42 GMT

do you get it or do you need further help?

Re: Create Cumulative Count of Records in script by date

mikelees80 — Mon, 14 Aug 2017 09:48:10 GMT

I'm trying it now Robin

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

Re: Create Cumulative Count of Records in script by date

devarasu07 — Mon, 14 Aug 2017 10:04:09 GMT

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;

Re: Create Cumulative Count of Records in script by date

mikelees80 — Mon, 14 Aug 2017 10:08:28 GMT

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

Re: Create Cumulative Count of Records in script by date

antoniotiman — Mon, 14 Aug 2017 10:10:24 GMT

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

Re: Create Cumulative Count of Records in script by date

Kushal_Chawda — Mon, 14 Aug 2017 11:11:05 GMT

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;

Re: Create Cumulative Count of Records in script by date

Kushal_Chawda — Mon, 14 Aug 2017 11:20:16 GMT

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;

Re: Create Cumulative Count of Records in script by date

mikelees80 — Mon, 14 Aug 2017 13:19:49 GMT

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

Re: Create Cumulative Count of Records in script by date

Kushal_Chawda — Mon, 14 Aug 2017 13:30:32 GMT

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?

Re: Create Cumulative Count of Records in script by date

mikelees80 — Mon, 14 Aug 2017 13:32:20 GMT

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

Re: Create Cumulative Count of Records in script by date

Kushal_Chawda — Mon, 14 Aug 2017 13:45:09 GMT

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;

Re: Create Cumulative Count of Records in script by date

mikelees80 — Mon, 14 Aug 2017 15:16:24 GMT

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;

Re: Create Cumulative Count of Records in script by date

Anonymous — Mon, 14 Aug 2017 15:19:47 GMT

.. you asked for a script solution...

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

Re: Create Cumulative Count of Records in script by date

mikelees80 — Mon, 14 Aug 2017 15:22:52 GMT

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

Re: Create Cumulative Count of Records in script by date

Kushal_Chawda — Mon, 14 Aug 2017 15:32:07 GMT

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;