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.

[Gysbert_Wassenaar]

Can you post a small qlikview document that demonstrates the problem?

[Mark_Little]

Hi,

I might not be following your requirements correctly.

But could you not add a Flag in your script.

    IF(ISNULL(OpenDate) = 0 AND ISNULL(ClosedDate) = 0, 0,1) as Open

Then just do a count of the Flag?

Mark

[Anonymous]

Here is the Qlikviw of what I am trying to achieve. As you can see the cases with no end date are not showing.

[Anonymous]

Hi Mark, I like the thinking but when I tried it I got the same results as I did with my script. I was applying this to the reference.month. I also tried it linked to the normal master calendar but I just got the initial number of cases being opened in that month.

[Mark_Little]

Hi,

I am might be missing the point but take a look at this attached and see if this is what you are wanting?

I am making a flag where the reference date is less than End_Support_Date.

Mark

[Gysbert_Wassenaar]

That's because an inline load can't create nulls, only blanks. So your IsNull test fails. Try using the alt function instead: While IterNo() <=Alt(END_SUPPORT_DATE,Today()) - REFERRALDATE +1;

[Anonymous]

I gave it a try but it is making a count for each ID for each day in each month which is giving me a huge number of cases. Im thinking I may need to be using the month instead of the day in my Cases x Dates table.

[Anonymous]

Hi, thanks for that (I didn't know that) but the inline load is only for the test qlikview. I also tried your metod on my full load script but it did not change the chart output.

[Mark_Little]

Hi Clive,

Are you wanting a chart that show what cases were not closed in that month?

If so you can just wrap the if statement i gave in my example before in the Month() function.

[Cases_x_Dates]:

Load *, IF(MONTH(ReferenceDate) < MONTH(ESD), 1, 0) as Open;

load CASE_ID,

Date(REFERRALDATE +IterNo()-1) as ReferenceDate,

END_SUPPORT_DATE as ESD

Resident REFERRAL

While IterNo() <=if(isnull(END_SUPPORT_DATE),Today(),END_SUPPORT_DATE) - REFERRALDATE +1;

And that give the below results.