Count Conditional Active Records by Month

[#1]

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,

M

[Gysbert_Wassenaar]

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

[#3]

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!

M

[Gysbert_Wassenaar]

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.