You need to sign in to do that
Don't have an account?
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
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!
All Answers
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...
ISPICKVAL(PRIORVALUE(Status), "Closed - Resolved"
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.
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!