How to display number of open cases in a month

[Anonymous]

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.

[MarcoWedel]

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

[MarcoWedel · Accepted solution]

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

[Anonymous]

Hi Marco,

Thanks for that it worked great. Nice little tip about the set nullinterpret as well thank you.

[Anonymous]

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.