ShowAll Questionssorted byDate Posted
Mr Sling

# Case history field formula (or report formula) for tracking re-opened cases

We're trying to track FCR a bit better than the default "age < 24 hours" case report.  We'd like to be able to filter out cases that have ever been re-opened (Changed from "Closed - Resolved" to "In Progress"), or create a case formula "FCR" field that tracks:

Case age > 24 --> FCR = "0" or "False"

Case age < 24 --> FCR = "1" or "True"

Case Status ever changed from "Closed" to "In Progress" --> FCR = "0" or "False"

Or, if there's any simpler way to do this that I haven't considered, why reinvent the wheel?  :-)

Many thanks!

Mark

Mr Sling

Quick update: I was able to create custom Case formula fields which work for us and thought I'd share.  As usual, I was making it a bit too complicated -- I ended up simplifying it down to 2 easier, reportable, values.

1) FCR within 24 Hours - "1" if the case was closed within 24 hours. "0" if it was closed after 24 hours

IF((MOD((ClosedDate - CreatedDate),24) < 1) || (CreatedDate = ClosedDate), 1 , 0)

I had to do the "OR" above because I found that cases created/closed at the same time were giving strange results, so this forces the value in that situation to "1".  Also, I had to put the MOD statement in to get it to a 24 hour period, otherwise a case created at 11:59 PM and closed at 12:01 AM would be listed as "0".

2) FCR after 24 Hours - "1" if the Case was NEVER touched after 24 hours of Case creation, "0" if it was.

IF((LastModifiedDate > CreatedDate + 1) || (IsClosed = false), 0, 1)

I think I'm OK here without having to do the MOD statement on the 2nd field.  We'll see if i find some scenarios which require me to revisit it...

Now that we have this data-point, we can start using it to cross-reference FCR values by Affected Product/Agent/Type, etc.  Woot!

Mr Sling

After taking some time away from FCR, I'm looking at it again and am still having difficulties.  :-)  To elaborate, I've tried 2 different approaches and am seemingly hitting a wall with both...

1) Create a new Case Formula Field (as an independent reporting element) which reports "1" if a case is closed within 24 hours but will change to "0" if the case is ever reopened.  I tried simply starting with the following as an 'IF' statement, but even this simple syntax is reporting 'not allowed' in this type of formula...?

ISPICKVAL(PRIORVALUE(Status), "Closed - Resolved"

2) Modify the 'vanilla' case history report --> it already offers...
Field / Event Old Value New Value

Status Closed - Resolved In Progress

... but I can't seem to tweak these at the reporting criteria or custom (report) formula level to either hide the above example or call it out specifically.

ANY help or suggestions for either 2 approaches above would be greatly appreciated!

Mr Sling

Quick update: I was able to create custom Case formula fields which work for us and thought I'd share.  As usual, I was making it a bit too complicated -- I ended up simplifying it down to 2 easier, reportable, values.

1) FCR within 24 Hours - "1" if the case was closed within 24 hours. "0" if it was closed after 24 hours

IF((MOD((ClosedDate - CreatedDate),24) < 1) || (CreatedDate = ClosedDate), 1 , 0)

I had to do the "OR" above because I found that cases created/closed at the same time were giving strange results, so this forces the value in that situation to "1".  Also, I had to put the MOD statement in to get it to a 24 hour period, otherwise a case created at 11:59 PM and closed at 12:01 AM would be listed as "0".

2) FCR after 24 Hours - "1" if the Case was NEVER touched after 24 hours of Case creation, "0" if it was.

IF((LastModifiedDate > CreatedDate + 1) || (IsClosed = false), 0, 1)

I think I'm OK here without having to do the MOD statement on the 2nd field.  We'll see if i find some scenarios which require me to revisit it...

Now that we have this data-point, we can start using it to cross-reference FCR values by Affected Product/Agent/Type, etc.  Woot!

This was selected as the best answer