DATE_DIFF() Between 2 Custom Attributes

5 Comments

  • Kerry Charlton

    This was possible to create in Insights, can the ETA please be expedited for the current bug so this is achievable in Explore.

    2
  • Derrek Jennings

    I have a similar request, with a small difference, I need to figure the diff between when a custom field is updated and when the ticket was created, in business hours.

    0
  • Kerry Charlton

    Derrek Jennings - This is possible, you need to create a standard calculated attribute.

    This is for minutes - ROUND(DATE_DIFF([ATTRIBUTE],[Ticket created - Timestamp],"nb_of_minutes"))

    This is for hours - ROUND(DATE_DIFF([ATTRIBUTE],[Ticket created - Timestamp],"nb_of_hours"))

    The attribute is the custom ticket field you want to want to report against

     

     

     

    1
  • Derrek Jennings

    Great, thank you very much Kerry Charlton! I had a modified formula based on some google searches, but was not as advanced as what you provided.

    Your suggestion is returning results, thank you.

    I tried changing the "nb_of_hours" to be "Business hours (min)/60". Is nb_of_hours straight hours? I thought I read that it was, so thought I'd try business hours, but I am getting no results. Not sure if that would be related at all to the issue I'm waiting on ZD support to find out why the timestamp on our custom date field always has the time portions as 0.

     

    Thanks again Kerry, very much appreciated!

    0
  • Joanna

    Derrek Jennings to get the timestamp when the ticket field was updated you would have to create a custom attribute using a formula similar to this, created in the Ticket Updates dataset:

    IF ([Changes - Field name]="CHECKBOX NAME")
    AND
    (([Changes - Previous value]="0") OR
    ([Changes - Previous value]=NULL))
    AND
    ([Changes - New value]="1")
    THEN
    [Update - Timestamp]
    ENDIF

    If the ticket field is not a checkbox you could use a formula similar to this, once again this would have to be created in the Ticket Updates dataset.

    IF ([Changes - Field name]="FIELD NAME")
    AND
    ([Changes - Previous value]=NULL)
    AND
    ([Changes - New value]!=NULL)
    THEN
    [Update - Timestamp]
    ENDIF

     

    Once you have the ticket field updated timestamp you will need to follow Kerry Charlton's formula to get the date difference between the newly created attribute and the created date timestamp.

    When extracting the date difference between your ticket field being updated and the ticket being created 'nb_of_hours' would be the correct date_part to use. 

    To my knowledge it is not possible to return this date difference in business hours, but you may want to reach out to Zendesk directly to confirm that. 

    You can read more about DATE_DIFF here

    0

Please sign in to leave a comment.

Powered by Zendesk