Open cases metric

[#1]

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

[rupamjyotidas]

Cases closeD:

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

Case Open:

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

[#3]

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]

Open Cases:

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

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

sum(Len(Date_Closed)<1,Cases)

[#5]

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.

[#6]

Can anyone help with this?

[Anonymous]

Hi Sergio,

Can you share sample raw data?

[rupamjyotidas]

Something like this.

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

[#9]

Please see attached example