Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am trying (and failing) to create a graph that shows the number of open cases within any given month. The cases have an open date and some have a close date but this is only entered at the point of closing so in some cases this field is null. The cases can be open for a period of several months (so I would like to show a count of 1 for that case in each month it is open even if it is only 1 day). I have tried creating reference dates for the period that the case is open which provides a list of cases open on each day. (using hic Creating Reference Dates for Intervals, That almost worked and I just needed to tweak it to use the Today function if there was no end date) When I try to display this as a monthly calendar it accumulates the total, see graphs below. I am expecting to see around 180 cases open in any particular month.
Code to create above table and master calendar
load CASE_ID,
Date(REFERRALDATE +IterNo()-1) as ReferenceDate
Resident GENTOO_WELLBEING_REFERRAL
While IterNo() <=if(isnull(END_SUPPORT_DATE),Today(),END_SUPPORT_DATE) - REFERRALDATE +1;
[Cases Open Calendar]:
LOAD ReferenceDate AS ReferenceDate,
trim(date(date(ReferenceDate,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD/MM/YYYY ') )AS [ReferenceDate.Date],
Month(ReferenceDate) As [ReferenceDate.Month],
Year(ReferenceDate) As [ReferenceDate.Year]
RESIDENT [Cases_x_Dates];
Dimension used in graph is ReferenceDate.Month and Expression is Count(distinct CASE_ID)
Any ideas or pointers would be appreciated as I do not know if I am even using the correct methodology.
Hi,
also possible:
[REFERRAL]:
LOAD * INLINE [
CASE_ID, REFERRALDATE, END_SUPPORT_DATE
1, 01/07/2015, 02/08/2015
2, 02/07/2015, 20/07/2015
3, 05/07/2015, 05/09/2015
4, 05/08/2015, 02/09/2015
5, 26/07/2015, 25/08/2015
6, 07/08/2015,
7, 18/07/2015,
];
[Cases_x_Dates]:
load CASE_ID,
Date(REFERRALDATE+IterNo()-1) as ReferenceDate
Resident REFERRAL
While REFERRALDATE+IterNo()-1 <= RangeMin(END_SUPPORT_DATE,Today());
[Cases Open Calendar]:
LOAD ReferenceDate AS ReferenceDate,
trim(date(date(ReferenceDate,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD/MM/YYYY ') )AS [ReferenceDate.Date],
Month(ReferenceDate) As [ReferenceDate.Month],
Year(ReferenceDate) As [ReferenceDate.Year]
RESIDENT [Cases_x_Dates];
hope this helps
regards
Marco
Hi again,
to make your example work and load null values from your inline table, you just have to add this line to your script:
SET NullInterpret ='';
hope this helps
regards
Marco
Hi Marco,
Thanks for that it worked great. Nice little tip about the set nullinterpret as well thank you.
Hi Mark,
Thanks for your help on this one. I think your answer was correct as well but when I tried it out I did not see the results I expected. Only after trying Marco's did I revisit the data set and found that some of the cases had not been closed correctly resulting in a huge distortion in the results.