In this article, you'll learn about using Explore date functions that help you to focus your calculations on the first or the last event in a series.
Typically, when you report on specific events or the time between them, some of the events are not unique. For example, one ticket can have multiple resolution events. The Explore earliest and latest date functions help you find unique first and last events.
Explore features the following four earliest and latest date functions:
- DATE_FIRST(time attribute)
Returns the earliest date or timestamp according to attributes added to the report and is affected by all applied filters.
Example:DATE_FIRST([更新时间戳)
Returns the earliest update timestamp taking into account all attributes you added to the report.
- DATE_LAST(time attribute)
Returns the latest date or timestamp according to attributes added to the report and is affected by all applied filters.
Example:DATE_LAST([Update - Timestamp])
Returns the latest update timestamp taking into account all attributes you added to the report.
- DATE_FIRST_FIX(time attribute, attribute1, attribute2, ...)
Returns the earliest date or timestamp according to the attributes specified in the function. Attributes added to the report will not affect the calculation but any filters applied will be taken into account.
Example:DATE_FIRST_FIX([Update - Timestamp], [Update ticket ID])
Returns the earliest update timestamp per ticket, regardless of the attributes from the report.
- DATE_LAST_FIX(time attribute, attribute1, attribute2, ...)
Returns the latest date or timestamp according to the attributes specified in the function. Attributes added to the report will not affect the calculation but any filters applied will be taken into account.
Example:DATE_LAST_FIX([Update - Timestamp], [Update ticket ID])
Returns the latest update timestamp per ticket, regardless of the attributes from the report.
Examples for using earliest and latest date functions
This section contains the following examples:
- Using the DATE_LAST function to find tickets solved on the last day of the month for a group
- Using the DATE_FIRST function to find the earliest daily ticket assignment
- 使用DATE_LAST_FIX傅nction to find the final ticket resolution date
- 使用DATE_LAST_FIX傅nction to find the latest agent comment time
- 使用DATE_FIRST_FIX函数找到冷杉t internal comment time
Using the DATE_LAST function to find tickets solved on the last day of the month for a group
In this example you'll use the DATE_LAST function to create a report that returns tickets solved on the last date, then by adding year, month and group attributes to the report you will get resolutions for the last date of the month for each group.
To create the report
- Create a new report using theSupport: Ticketsdataset.
- Create a standard calculated metric namedTickets solved on last date和the following formula:
IF DATE_LAST([Ticket solved - Date])=[Ticket solved - Date] THEN [Ticket ID] ENDIF
- Edit the metric you just created and set its default aggregator toCOUNT. Remove any other aggregators.
- Add theTicketsandTickets solved on last datemetric to theMetricspanel of the report builder.
- In theRowspanel, add theTicket solved - YearandTicket solved - Monthattributes. You’ll see total number of solved tickets and tickets solved on the last day of the month, for example:
- In theRowspanel, add the票组属性。You’ll see that the tickets solved on the last date are determined per group in addition to year and month:
Using the DATE_FIRST function to find the earliest daily ticket assignment
In this example you'll use the DATE_FIRST function to create a report that returns the earliest ticket assignment timestamp. Then, you'll add the assignee and date attributes to get the earliest timestamp per date and assignee.
To create the report
- Create a new report using theSupport: Ticketsdataset.
- Create a standard calculated attribute namedEarliest assignment - timestamp和the following formula:
IF DATE_FIRST([Ticket first assigned - Timestamp])=[Ticket first assigned - Timestamp] THEN [Ticket first assigned - Timestamp] ENDIF
- Add theTicketsmetric to theMetricspanel of the report builder.
- In theRowspanel, add theAssignee nameandTicket assigned - Dateattributes. Configure the second attribute to show tickets created this week.
- In theRowspanel, add theEarliest assignment - timestampattribute, then filter it by excluding NULL values. You’ll see the earliest daily ticket assignments by assignee and date, for example:
使用DATE_LAST_FIX傅nction to find the final ticket resolution date
Tickets can be resolved and reopened multiple times. In some cases, you only want to see the latest resolution. In this example you'll use the DATE_LAST_FIX function to produce a metric that will return the number of final ticket resolutions.
To create the report
- Create a new report using theSupport: Updates historydataset.
- Create a standard calculated metric namedFinal resolutions和the following formula:
IF ([Changes - Field name]="status" AND [Changes - Previous value]!="solved" AND ([Changes - New value]="solved" OR [Changes - New value]="closed") AND DATE_LAST_FIX([Update - Timestamp], [Update ticket ID], [Changes - Field name], [Changes - New value])=[Update - Timestamp]) THEN [Update ID] ENDIF
- Edit the metric you just have created and set its default aggregator toCOUNT. You can also remove other aggregators.
- Add theResolutionsandFinal resolutionsmetrics to theMetricspanel of the report builder.
- In theFilterspanel, add theUpdate - Dateattribute and configure this to show updates in the last 30 days. Your report will return the total number of resolutions and number of final resolutions. You can slice these metrics using any attributes, for exampleUpdate ticket group.
使用DATE_LAST_FIX傅nction to find the latest agent comment time
In this example you'll use the DATE_LAST_FIX function to create an attribute that will return the latest agent comment timestamps that will not be sliced by the attributes from the report. This report can help you detect agent seats that were not used for a long time.
To create the report
- Create a new report using theSupport: Updates historydataset.
- Create a standard calculated attribute namedLatest agent comment - timestamp和the following formula and enable theSort like time attributesetting:
IF ([Comment present]=TRUE AND [Comment public]=TRUE AND DATE_LAST_FIX([Update - Timestamp],[Comment present],[Comment public],[Updater name])=[Update - Timestamp]) THEN [Update - Timestamp] ENDIF
- Add theCommentsmetric to theMetricspanel of the report builder.
- In theFilterspanel, add theTicket created - Dateattribute and configure this to show updates in the last week or month.
- In theFilterspanel, add theUpdater roleattribute and configure this to showAgentsandAdmins.
- In theRowspanel, add theUpdater name属性。
- Finally, in theRowspanel, add theLatest agent comment - timestampattribute, then filter it to exclude NULL values. You’ll see the list of agents and their latest comment timestamps:
使用DATE_FIRST_FIX函数找到冷杉t internal comment time
One of the default metrics available in the Tickets dataset is First reply time. It gives you insight on how long it took for an agent to respond to the end user. Some customers have internal processes where an internal comment is added to the ticket before a public reply.
In this example, you'll use the DATE_FIRST_FIX function to create a report that returns the average first internal comment time by month.
To create the report
- Create a new report using theSupport: Updates historydataset.
- Create a standard calculated attribute namedFirst internal comment - Timestamp和the following formula and enable theSort like time attributesetting:
IF ([Comment present]=TRUE AND [Comment public]=FALSE AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Comment present],[Comment public])=[Update - Timestamp]) THEN [Update - Timestamp] ENDIF
- Create a standard calculated metric namedFirst internal comment time (hrs)和the following formula:
DATE_DIFF([First internal comment - Timestamp], [Ticket created - Timestamp], "nb_of_hours")
- Edit the metric you just created, set it's default aggregator toAVG, display format toCustom, and setDecimal pointsto1andSuffixtohrs
- In theFilterspanel, add theTicket created - Dateattribute and configure this to show updates in the last three months.
- Add theFirst internal comment time (hrs)metric to theMetricspanel of the report builder.
- In theColumnspanel, add theTicket created - Monthattribute and configure this to show updates in the last 6 months.
- Finally, in theVisualization typesmenu, select theColumnchart. You’ll see the first internal comment time by month:
35 Comments
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
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
Thanks for the info Darenne. I am actually looking for the last update by theassigned agentof the ticket not just by any agent.
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!
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. SeeDATE_LAST_FIX functionandCreating 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:
After saving it, I created a calculated metric using the formula outlined under Creating a last refresh timestamp article:
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:
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:
@...
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?
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
Last ticket created date
Once done, you can addUpdatesin the Metrics and underRows,addRequester, Ticket Group,and the 2 attributes we created.
You can then hide theUpdatescolumn by going toChart configuration>Columns> look for the Columns below and click the "eye" icon underVisible
Reference:Can I hide the metrics column in an Explore query table?
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.
Thank you for clarifying. We will probably have to edit the formula by adding an IF clause to it like this:
This way, we are only getting the Date for the last update if it was made by the ticket requester.
HiDarenne,
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
HiEugene OrmanandDarenne,
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):
你能帮我得到一个,最早Ti吗mestamp per ticket?
I have the same issue, DATE_FIRST returns multiple values per Ticket ID.
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:
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
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!
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:
Thanks for the example here. I've been looking into the formula that you provided and it looks like this is working as designed.
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.
HiCJ 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.
HiMarcoandAndrei 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:
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.
CJ JohnsonYour case looks pretty complex!
Could you try this version of the formula (without [Changes - Field name] in the DATE_FIRST_FIX)?
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
Hello All,
Can someone please help me - I want to know the new ticket group created in last 3 months.
Thanks,
Ravindra Singh
If you're on the Enterprise plan you should be able to track who created a group through the audit log as mentioned here:Viewing the audit log for changes
I hope this helps!
Could l be provided with a formula that we can get timestamps on tickets when they are escalated from team to team when a ticket is handed off as well as the time an agent would have held the ticket?
I think you might be able to adapt this recipe:Explore recipe: Recording the time when a checkbox was checked, but using the group change part of the formula fromExplore recipe: Tracking ticket assigns across groups(so, creating aStandard Calculated Attributeusing theSupport - Updates Historydataset, but using the group change logic to update the timestamp.
Hello Everyone,
Can someone please help on my requirement. I just want to get the last updated comment date & time on reports. Above metric which was provided is not working for me.
Hello Everyone,
How to display the last public comment made by the agent EVEN if there is other internal comment on the ticket after the public comment ?
You can try modifying the sample formula discussedhereby replacing Updater name with [Update ticket ID].
Then filter your report by Update role to only include 'Agent' and 'Admin'. With that attribute and the role filter, and your report sliced by Ticket ID, the table should show the timestamps of the most recent public reply by an agent on each ticket.
Hello,
We are using Zendesk Enterprise for technical support.
We have around 25% of our tickets that are automaticaly closed after some rules because the customer never reply to us, also when we provided the solution to the customer (reply in pending status) it may takes few days to have a confirmation and solve the ticket.
With this I would like to have create a different resolution duration indicator based on the last public comment - a duration indicator based on last public comment minus creation date.
Based on this article I managed to have for closed ticket the timestamp for the agent last public comment.
1) How can I retrieve the ticket creation or ticket Full resolution Time stamp?
2) Is there an easyest way to figure out the indicator I am looking for (duration indicator based on last public comment minus creation date) in hours?
3) Is it possible to have it in business hours?
Best regards
If you were able to create a custom attribute that pulls the timestamp of the last agent comment, then you may use the functiondate_diffto calculate the duration from the native attribute Ticket created - Timestamp to your custom timestamp attribute. But unfortunately, the calculation can only return results in calendar hours.
Pleasesign into leave a comment.