Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.