Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Charlie Mopps
    Why is rel_date_diff so crippled?
    Topic posted March 7, 2018 by Charlie MoppsGold Medal: 3,500+ Points 
    137 Views, 4 Comments
    Why is rel_date_diff so crippled?

    We've several hundred teams all with different schedules and hours of operations. Many of the managers want us to calculate “Time to resolve” excluding Nights and weekends. But due to the crazy restrictions on rel_date_diff I can’t even time_format the field, much less do an average. And because we’ve multiple teams with multiple shifts, the entire SLA system is a no-go for us, because it applies the SLA to the contact/org instead of the profile/queue/group.


    I found that someone had suggested improvements in the idea lab 4 years ago but it’s never been fix. Has anyone found a way around this? 



    • Carl Elliott

      I agree with you on the rel_date_diff function.  If you apply the SLA to the incident you can apply it by queue or profile.  We did something similar by applying SLAs by the category/Queue.  Yes it does have to create an instance for the contact so the contact will end up with many SLA instances but when measuring against the incident it does work. 

    • Charlie Mopps

      Now I have to read up on what SLA instances are. Thanks for the tip.

    • Carl Elliott

      The SLA instance is what ties the Contact to the SLA.  So a contact can have multiple SLA instances.  It also allows an SLA that has been changed to have the original SLA remain in effect for a customer.  When you apply an SLA to an incident there is a checkbox in the rule that says create a new instance if one doesn't exist. You want that to be yes so it will create the instance for the contact automatically.  If there is not an SLA instance for the contact for the SLA you are applying to an incident it will not apply it.  

      The whole point is you can apply an SLA to an incident and not have to worry too much about what SLA the contact record has.  And by applying them to the SLA you can measure at the SLA level.  There is a lot of articles on this in the KB. 

    • Gursimran Singh Saini

      Actually you can still do a lot with rel_date_diff

      1. After calculating ref_date_diff(), Edit Column Format -> Change Number to Seconds -> Now the Date Diff will come in Hours Minutes Seconds

      2. You can calculate overall / rollup average on rel_date_diff column by going to Edit Calculations.

      3. Although you cannot use rel_date_diff within an expression, BUT, you can use expressions within rel_date_diff
      For example:
      This is not allowed -> avg(rel_date_diff())
      This is allowed -> rel_date_diff(max(date1), if(condition, then, else))