Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

cliveketley
New Contributor

How to display number of open cases in a month

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.


ScreenShot 11-10-15 at 10.49.47 AM.png

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.

1 Solution

Accepted Solutions

Re: How to display number of open cases in a month

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 ='';

QlikCommunity_Thread_190958_Pic3.JPG

hope this helps

regards

Marco

13 Replies

Re: How to display number of open cases in a month

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


talk is cheap, supply exceeds demand
mark6505
Valued Contributor III

Re: How to display number of open cases in a month

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

cliveketley
New Contributor

Re: How to display number of open cases in a month

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

cliveketley
New Contributor

Re: How to display number of open cases in a month

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.

mark6505
Valued Contributor III

Re: How to display number of open cases in a month

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

Re: How to display number of open cases in a month

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;


talk is cheap, supply exceeds demand
cliveketley
New Contributor

Re: How to display number of open cases in a month

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.

cliveketley
New Contributor

Re: How to display number of open cases in a month

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.

mark6505
Valued Contributor III

Re: How to display number of open cases in a month

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.

Capture.JPG

Community Browser