Working with earliest and latest date functions

Return to top

10 Comments

  • Bill Cicchetti

    Nice to see more options for reporting.

    Could this be used to find the following?

    Date of last public comment and total days since last public comment by the assignee

    0
  • Darenne

    Hi Bill,

    With regard to your concern about the Date of the last comment made by an agent, it appears that this can be answered from one of the topics outlined in this article. For your reference, you may check out this topic: https://support.zendesk.com/hc/en-us/articles/1260801335150-Working-with-earliest-and-latest-date-functions#topic_mcb_wdy_5pb

    As for your concern about the total days since the last public comment made by an agent, it seems that this is achievable by using the custom metric to calculate the difference in date: DATE_DIFF(TODAY(),[custom attribute],"nb_of_days")

    For more information, you may also refer into this article: https://support.zendesk.com/hc/en-us/articles/360026058233-Explore-recipe-Days-since-an-organization-last-submitted-a-ticket.

    Best Regards,

    Darenne Carbajosa
    NEW - Zendesk offers free, on-demand training for all of our products. Set up your account and start learning today at training.zendesk.com

    0
  • Bill Cicchetti

    Thanks for the info Darenne.  I am actually looking for the last update by the assigned agent of the ticket not just by any agent.

    0
  • Crawford Philleo

    Hey there, I'm experimenting with the DATE_FIRST_FIX function and finding that I get very different results if I use "Ticket Solved" as my date filter in the query (rather than Ticket Created date, as your example uses). Why would that be the case? 

    On my query, I want to visualize "Of these solved tickets, this was the average first-response time". My attribute/metric combo here is in an effort to get a customized version of "First Reply Time" that skips over public-comment updates that result from a ticket merge.

    Thanks!

    0
  • Darenne

    Hi Bill,

    I've played around with the formula and it appears that this is possible by using the formulas from the articles I sent you. See DATE_LAST_FIX function and Creating the last refresh timestamp

    First, what I did was I first created a calculated attribute from the Ticket updates dataset and used the formula stated which was discussed in the DATE_LAST_FIX function article:

    IF ([Comment present] = TRUE AND [Updater role]! = "End -user" AND DATE_LAST_FIX ([Update - Timestamp], [Assignee name], [Updater role], [Comment present]) = [Update - Timestamp] ) THEN
    [Update - Timestamp]
    ENDIF

    After saving it, I created a calculated metric using the formula outlined under Creating a last refresh timestamp article:

    DATE_DIFF (now (), [Latest agent comment time (mins ago)], "nb_of_minutes")

    Just notice that I did not use the Ticket updated-Timestamp as discussed in the article, I used the "Latest agent comment time (mins ago)" because this is the title of the calculated attribute I first created. I just linked/integrated the 2 formulas so that they can provide proper data.

    As for the calculated attribute that I first created, I just created it but I will not use it under attribute. I made it just to link to the calculated metric to work well. After that, I created another calculated attribute using the formula:

    IF [Update ticket assignee] = [Assignee name] THEN "true"
    ELSE "false"
    ENDIF

    Enter whatever name you want for that attribute and save it. After saving it, I used it through Filters. I just added Comments under metrics while I used ticket ID, Assignee name, and Updater name in the attribute. Although the use of the Updater name attribute is optional, I only used it to validate the last update timestamp showing would reflect the assignee as the updater. You may refer to the screenshot below for your reference: 

    0
  • Bill Cicchetti

    @...

    I'll check it out. Thanks for all the work put in on this.  Much appreciated!
     
     
    0
  • Chetan Kudalkar

    I want to get the Last ticket update date for requester?

    I need following fields:

    Requester Name
    Ticket Group
    Last ticket update date
    Last ticket created date

    So each requester will have only one record, I tried many different ways but was not able to build the required matrix, can someone please help me to achieve this?

     

    0
  • Giuseppe

    Hi Chetan,

    You could probably set this up by creating a query with Ticket Updates dataset. From there, you can create 2 calculated attributes:

    Last ticket update date

    DATE_LAST([Ticket updated - Date])

    Last ticket created date

    DATE_LAST([Ticket created - Date])

    Once done, you can add Updates in the Metrics and under Rows, add Requester, Ticket Group, and the 2 attributes we created.

    You can then hide the Updates column by going to Chart configuration Columns > look for the Columns below and click the "eye" icon under Visible

    Reference: Can I hide the metrics column in an Explore query table?

    1
  • Chetan Kudalkar

    Hi,

    I did create it exactly same way as you mentioned but problem is  DATE_LAST([Ticket updated - Date]) returns me tickets last updated date. I want the date when customer last updated the comment.

     

    0
  • Giuseppe

    Thank you for clarifying. We will probably have to edit the formula by adding an IF clause to it like this:

     

    IF ((DATE_LAST([Ticket updated - Date]) = [Ticket updated - Date]) AND ([Updater name] = [Requester name])) THEN DATE_LAST([Ticket updated - Date]) ENDIF

    This way, we are only getting the Date for the last update if it was made by the ticket requester. 

    0

Please sign in to leave a comment.

Powered by Zendesk