topic Re: Open cases metric in QlikView

Open cases metric

Wed, 25 Nov 2020 16:16:04 GMT

I have two columns Date_Opened and Date_Closed.

For a certain SLA metric I need to calculate number of cases closed within certain month / total number of open cases at the end of that month (accumulated).

e.g. : for a month of July 2016 I need Cases Closed in July 2016 / All cases with no Date_Closed on 7/31/2016

Re: Open cases metric

rupamjyotidas — Wed, 30 Nov 2016 19:29:14 GMT

Cases closeD:

sum({<,Date_Closed= {">$(=(MonthEnd(AddMonths(Date_Closed,-1))))<$(=(MonthStart(AddMonths(Date_Closed,0)))))"}>} Cases)

Case Open:

sum({<,Date_Closed -= {"*"}>} Cases)

Re: Open cases metric

Wed, 30 Nov 2016 19:36:35 GMT

Cases Closed in a month I can count easily.

How do I find total number of open cases at the end of each month?

Re: Open cases metric

rupamjyotidas — Wed, 30 Nov 2016 19:42:56 GMT

Open Cases:

sum({<Date_Closed -= {"*"}>} Cases) or

sum( if (ISNULL(Date_Closed),Cases)) or

sum(Len(Date_Closed)<1,Cases)

Re: Open cases metric

Wed, 30 Nov 2016 20:04:17 GMT

Thank you for your answer Rupam, but it looks like my approach was incorrect.

Date_Closed field will eventually be populated, so even if it was empty on 7/31, case might be closed on 8/10.

All I have is one point in time table created at the end of the year.

I am assuming some if statement is needed using MonthEnd function.

Re: Open cases metric

Thu, 01 Dec 2016 13:30:46 GMT

Can anyone help with this?

Re: Open cases metric

Anonymous — Thu, 01 Dec 2016 13:38:00 GMT

Hi Sergio,

Can you share sample raw data?

Re: Open cases metric

rupamjyotidas — Thu, 01 Dec 2016 13:39:01 GMT

Something like this.

sum({<Date_Opened= {"<$(=(MonthEnd(AddMonths(Date_Opened,0))))"}, {<Date_Closed -= {"*"}>} Cases)

Re: Open cases metric

Thu, 01 Dec 2016 14:17:15 GMT

Please see attached example