
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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
- Tags:
- qlikview_scripting
- « Previous Replies
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
if different accounts should be separated?!?
then you should add a further condition like
if (account <> previous(acount) ... ) ...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes I need to split the count by account

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
do you get it or do you need further help?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I'm trying it now Robin
Thanks for the advice and i'll let you know how I get on


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- « Previous Replies
- Next Replies »