topic Count Conditional Active Records by Month in QlikView

Count Conditional Active Records by Month

Thu, 11 Jul 2013 11:15:11 GMT

Hi All,

I have the below records in my table:

ID Status Status_change_date Start_date End_date

1 Completed 02/05/2012 01/01/2012 31/12/2013

2 Approved 17/02/2012 16/02/2014

3 Approved 03/05/2012 03/05/2014

4 Approved 07/04/2013 07/04/2015

5 Approved 04/12/2012 04/12/2014

6 Expired 11/03/2014 11/03/2012 11/03/2014

7 Approved 08/08/2012 08/08/2014

8 Approved 26/01/2013 26/01/2015

9 Completed 15/06/2013 21/02/2013 21/02/2015

10 Approved 14/06/2013 14/06/2015

11 Completed 10/03/2013 09/09/2012 09/09/2014

12 Completed 03/06/2013 02/05/2013 02/05/2015

13 Approved 29/12/2012 29/12/2014

14 Approved 02/06/2013 02/06/2015

15 Approved 04/04/2013 04/04/2015

Essentially, I need a count of active IDs per month. The condition being, if the status is completed, then use the Status_change_date as the ID end date, else use the End_date. I tried the below to count the active IDs for all months where an ID was present, but this logic is only restricted to Start_date and End_date and I need to try to incorporate the condition of the status change as described too... I only have QVPE, so I can't open any attached examples.

Test:

LOAD * ,

addmonths(monthstart(Start_date),IterNo()-1) as Month

while MonthStart(End_date)>=AddMonths(MonthStart(Start_date),IterNo()-1);

Any ideas would be appreciated!!!

Thanks,

Re: Count Conditional Active Records by Month

Gysbert_Wassenaar — Thu, 11 Jul 2013 11:51:44 GMT

Try this:

//example data, replace with the real load statement

Input:

LOAD

ID, Status,

date#(Status_change_date,'DD/MM/YYYY') as Status_change_date,

date#(Start_date,'DD/MM/YYYY') as Start_date,

date#(End_date,'DD/MM/YYYY') as End_date

INLINE [

ID, Status, Status_change_date, Start_date, End_date

1, Completed, 02/05/2012, 01/01/2012, 31/12/2013

2, Approved, , 17/02/2012, 16/02/2014

3, Approved, , 03/05/2012, 03/05/2014

4, Approved, , 07/04/2013, 07/04/2015

5, Approved, , 04/12/2012, 04/12/2014

6, Expired, 11/03/2014, 11/03/2012, 11/03/2014

7, Approved, 08/08/2012, 08/08/2014

8, Approved, 26/01/2013, 26/01/2015

9, Completed, 15/06/2013, 21/02/2013, 21/02/2015

10, Approved, , 14/06/2013, 14/06/2015

11, Completed, 10/03/2013, 09/09/2012, 09/09/2014

12, Completed, 03/06/2013, 02/05/2013, 02/05/2015

13, Approved, , 29/12/2012, 29/12/2014

14, Approved, , 02/06/2013, 02/06/2015

15, Approved, , 04/04/2013, 04/04/2015

];

// create a table with Months per ID

ID_Months:

Load ID, addmonths(monthstart( Start_date),IterNo()-1) as Month

resident Input

while addmonths(monthstart( Start_date),IterNo()-1) <= End_date;

You can now use Month as dimension in a chart and count(ID) as expression to get the active ID's per month

Re: Count Conditional Active Records by Month

Thu, 11 Jul 2013 15:17:10 GMT

Hey,

Thanks that last bit helped, but what I did was created an 'End_Date2' in the script which was as follows:

if((status='Completed' or status='Expired')and status_change_date<End_date, status_change_date,End_date) as End_Date2

Created a table with months per ID like you said.

Now I have another problem: out of the set of results I have, I need to say per Month/Year the ID was active, was it in it's 1st year, 2nd Year, ... Nth Year? Stumped.

Any ideas?

Thanks again!

Re: Count Conditional Active Records by Month

Gysbert_Wassenaar — Thu, 11 Jul 2013 16:07:21 GMT

You could use iterno to create a field Age that contains the number of months the ID is active:

ID_Months:

Load ID, addmonths(monthstart( Start_date),IterNo()-1) as Month,

iterno() as Age

resident Input

while addmonths(monthstart( Start_date),IterNo()-1) <= End_date;

Or you could create it as 1+floor(iterno()/12) to get the active years.