+ Start a Discussion
Mr SlingMr 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 

Best Answer chosen by Admin (Salesforce Developers) 
Mr SlingMr 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!

 

 

All Answers

Mr SlingMr 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 SlingMr 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