Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Open cases metric

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

8 Replies
rupamjyotidas
Specialist
Specialist

Cases closeD:

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

Case Open:

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

Not applicable
Author

Cases Closed in a month I can count easily.

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

rupamjyotidas
Specialist
Specialist

Open Cases:

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

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

sum(Len(Date_Closed)<1,Cases)

Not applicable
Author

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.

Not applicable
Author

Can anyone help with this?

payalgosar
Creator II
Creator II

Hi Sergio,

Can you share sample raw data?

rupamjyotidas
Specialist
Specialist

Something like this.

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

Not applicable
Author

Please see attached example