Working with earliest and latest date functions

Return to top

21 Comments

  • Giuseppe
    Zendesk Customer Care

    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
  • 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.

    1
  • Darenne
    Zendesk Customer Care

    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
  • Marco Malbas
    Zendesk Customer Care
    Hi CJ, 
     
    Could you please provide the formula that you are using? On Andrei's example above, the issue is happening because it is taking the timestamp for "Released" and "Done". Do you also have two possible value in yours? It would also be great if you can check a ticket showing two timestamps to see what "condition" they are meeting in the formula so I can help you out further. 
     
    Cheers! 
     
     
    0
  • Sophie Maini-Gastou

    Hi Darenne,

    Thanks for this complete step by step : https://support.zendesk.com/hc/en-us/articles/4408833381402/comments/4408849086618

    Anyway, how to display the last comment public made by the agent EVEN if there is other internal comment on the ticket after the public comment ?

    Thanks for you help

    0
  • Andrei Kamarouski
    Community Moderator
    The Wise One - 2021

    Hi Eugene Orman and Darenne,

    I am working on one use case and found one issue with the DATE_FIRST_FIX function. From your docs, it sounds like it should return single ("Returns the earliest update timestamp per ticket") but I receive multiple Timestapms per ticket. 

    The custom attribute is like this:


    IF ([Changes - Field name]="Jira Status"
    AND ([Changes - New value]="done" OR [Changes - New value]="closed_-_without_fix" OR [Changes - New value]="released")
    AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Changes - Field name],[Changes - New value])=[Update - Timestamp])
    THEN [Update - Timestamp]
    ENDIF

    This is what I get in the query (i.e. Ticket 16819 shows 2 timestamps - one for Released and one for Done changes in Jira Status ticket):

    Could you help me to get just one, the earliest Timestamp per ticket? 

    0
  • Marco Malbas
    Zendesk Customer Care
    Hi CJ, thanks for the update here and for checking it on your end. Can I have more information on the formula that you're using so that I can try replicating it on my test account? That would give me something to work with on troubleshooting this, see if there's anything I can do to help you out. :) 
    0
  • Marco Malbas
    Zendesk Customer Care
    Hi CJ,
     
    Thanks for the example here. I've been looking into the formula that you provided and it looks like this is working as designed.
    IF [Comment present]=TRUE
    AND [Comment public]=TRUE
    AND [Priority Escalation] = "P0 Escalation"
    AND [Updater role] != "End-user"
    AND [Changes - Field name] != "Priority Escalation"
    AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Comment present],[Comment public], [Updater role],[Priority Escalation],[Updater role],[Changes - Field name])=[Update - Timestamp]
    THEN [Update - Timestamp] ENDIF
    Not all of your DATE_FIRST_FIX parameters are only single possible values. Namely, this formula is looking to see if the Updater role is not end user. Then you are looking at the first dates of Updater roles. This will show the first update for an updater role of both admin and agent, hence some tickets have two values. 
     
    Hope this clarifies it for you. 
    0
  • Andrei Kamarouski
    Community Moderator
    The Wise One - 2021

    CJ Johnson Your case looks pretty complex! 😅

    Could you try this version of the formula (without [Changes - Field name] in the DATE_FIRST_FIX)? 

    IF [Comment present]=TRUE
    AND [Comment public]=TRUE
    AND [Priority Escalation] = "P0 Escalation"
    AND [Updater role] != "End-user"
    AND [Changes - Field name] != "Priority Escalation"
    AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Comment present],[Comment public], [Updater role],[Priority Escalation])=[Update - Timestamp]
    THEN [Update - Timestamp] ENDIF


    P.S. If you would like to try to solve this challenge - contact me via andrei[AT]pythia.cc. I just love solving Explore challenges very much 🤓

    0
  • Giuseppe
    Zendesk Customer Care

    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
  • CJ Johnson

    Hi Marco Malbas and Andrei Kamarouski, thanks for these tips! This did help me figure out why it's not working, but unfortunately, it also means that there is no way to accomplish my goal. I need to know the first comment *after* a custom field was updated. Because of how the Updates dataset is set up, I would need to do: 

    AND [Changes - Field name] != "Priority Escalation"

    But that inherently would have a *ton* of matches, because it will go "oh okay cool literally every other field change that happened is a match then." 

    It would seem there's simply no way to get the "first" Reply Time after a ticket was escalated, because of this. 

    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
  • 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
  • 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
  • Andrei Kamarouski
    Community Moderator
    The Wise One - 2021

    Hi CJ Johnson

    Recently I had a similar Explore task to define the timestamp of the latest agent comment. Having a very similar formula I had an issue that MANY last comment timestamps were given per single ticket! Finally, I was able to fix it by removing the [Updater role] attribute from the DATE_FIRST_FIX part of the formula. I hope this might help you. 

    0
  • Bill Cicchetti

    @...

    I'll check it out. Thanks for all the work put in on this.  Much appreciated!
     
     
    0
  • Darenne
    Zendesk Customer Care

    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
  • Anton vh

    I am struggling with the results that keep changing as the 'UPDATE - DATE' filter is updating. The DATE_FIRST_FIX returns the earliest date of updates within the filtered range. 

    Usecase:

    • Ticket is updated and assigned to group A in January
    • Ticket is updated and assigned to another group in February
    • Ticket is updated and assigned to group A in March

    If my UPDATE - DATE filters are set for the period January to March the first date = January which is correct. If my date filter is set from February to March my first date becomes March. I would like to have it always return in January. 

    I have the following:

    IF(
      [Changes - Field name]="group_id" 
      AND [Changes - New value]="groupid_a"
      AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Changes - Field name],[Changes - New value])=[Update - Timestamp]
      )
      THEN [Update - Timestamp]
      ENDIF

    0
  • CJ Johnson

    Sure, I finally have an example that I can share with this happening. I am trying to return the first time a public comment is left by an agent or admin, after a ticket has had a custom field "Priority Escalation" set to P0.  The agent will send a public comment when they do this kind of escalation, so I do NOT want the system to return the timestamp if it's the same update where the priority was changed, it must be after that. (This is why there's the Changes parts included). 

    In human terms, I want "If a public comment is left, and the priority escalation field is set to P0, and the commenter is not an end-user, and the field Priority Escalation wasn't changed during this update, return the earliest Update -Timestamp that is a match."

    IF [Comment present]=TRUE
    AND [Comment public]=TRUE
    AND [Priority Escalation] = "P0 Escalation" 
    AND [Updater role] != "End-user"
    AND [Changes - Field name] != "Priority Escalation"
    AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Comment present],[Comment public], [Updater role],[Priority Escalation],[Updater role],[Changes - Field name])=[Update - Timestamp]
    THEN [Update - Timestamp] ENDIF

    As you can see, there's still tickets showing multiple timestamps:

    0
  • CJ Johnson

    I have the same issue, DATE_FIRST returns multiple values per Ticket ID.

    0
  • 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

Please sign in to leave a comment.

Powered by Zendesk