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([Update - Timestamp])
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
- Using the DATE_LAST_FIX function to find the final ticket resolution date
- Using the DATE_LAST_FIX function to find the latest agent comment time
- Using the DATE_FIRST_FIX function to find the first 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 the Support: Tickets dataset.
- Create a standard calculated metric named Tickets solved on last date with 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 to COUNT. Remove any other aggregators.
- Add the Tickets and Tickets solved on last date metric to the Metrics panel of the report builder.
- In the Rows panel, add the Ticket solved - Year and Ticket solved - Month attributes. You’ll see total number of solved tickets and tickets solved on the last day of the month, for example:
- In the Rows panel, add the Ticket group attribute. 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 the Support: Tickets dataset.
- Create a standard calculated attribute named Earliest assignment - timestamp with the following formula:
IF DATE_FIRST([Ticket first assigned - Timestamp])=[Ticket first assigned - Timestamp] THEN [Ticket first assigned - Timestamp] ENDIF
- Add the Tickets metric to the Metrics panel of the report builder.
- In the Rows panel, add the Assignee name and Ticket assigned - Date attributes. Configure the second attribute to show tickets created this week.
- In the Rows panel, add the Earliest assignment - timestamp attribute, then filter it by excluding NULL values. You’ll see the earliest daily ticket assignments by assignee and date, for example:
Using the DATE_LAST_FIX function 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 the Support: Updates historydataset.
- Create a standard calculated metric named Final resolutions with 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 to COUNT. You can also remove other aggregators.
- Add the Resolutions and Final resolutions metrics to the Metrics panel of the report builder.
- In the Filters panel, add the Update - Date attribute 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 example Update ticket group.
Using the DATE_LAST_FIX function 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 the Support: Updates historydataset.
- Create a standard calculated attribute named Latest agent comment - timestamp with the following formula and enable the Sort like time attribute setting:
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 the Comments metric to the Metrics panel of the report builder.
- In the Filters panel, add the Ticket created - Date attribute and configure this to show updates in the last week or month.
- In the Filters panel, add the Updater role attribute and configure this to show Agents and Admins.
- In the Rows panel, add the Updater name attribute.
- Finally, in the Rows panel, add the Latest agent comment - timestamp attribute, then filter it to exclude NULL values. You’ll see the list of agents and their latest comment timestamps:
Using the DATE_FIRST_FIX function to find the first 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 the Support: Updates history dataset.
- Create a standard calculated attribute named First internal comment - Timestamp with the following formula and enable the Sort like time attribute setting:
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 named First internal comment time (hrs) with 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 to AVG, display format to Custom, and set Decimal points to 1 and Suffix to hrs
- In the Filters panel, add the Ticket created - Date attribute and configure this to show updates in the last three months.
- Add the First internal comment time (hrs) metric to the Metrics panel of the report builder.
- In the Columns panel, add the Ticket created - Month attribute and configure this to show updates in the last 6 months.
- Finally, in the Visualization types menu, select the Column chart. You’ll see the first internal comment time by month:
41 comments
Colin Hutzan
Hello!
I am trying to pull a report of tickets that have gone the longest without a public comment by an internal agent.
I have added a custom standard calculated attribute for latest public comment:
And I have built a report in the updates history dataset using the metric D_COUNT Tickets Updated w/public comment + the rows:
Latest public comment (this is what I named the calculated attribute)
Ticket ID
Ticket organization name
Ticket subject
Updater name
Assignee name
Filters:
Updater role: Agent, Admin
Ticket status: On hold
Comment public: exclude NULL and FALSE
The returned results seem to be correct (in that the rows containing a value in the latest public comment column have the correct value), but most of the rows are returning no values/NULL for the latest public comment column. Why would this be the case? What changes should I make for this report?
2
Colin Hutzan
Is it possibly to use DATE_FIRST to report on the first date an organization created a ticket?
Our use-case is that we want to understand ticket volumes for newly launched customers. I cannot go off of the date that the organization was added to ZenDesk, because we historically have added organizations when they sign contracts... it can be years before they actually launch.
So, the metric I need is 'first time existing organization submitted a ticket', set it to filter on dates within the last let's say 3 months, and then I would pull in ticket volumes by week for the organizations that fit the calculated metric.
Let me know if I am on the right track here or if this is something impossible.. or possible via another route. Thanks!
0
Jordan Means
I have been trying to build a report where it returns the timestamp that a ticket was first set to pending or on-hold but something isn't right as I am getting null values for some tickets that did have that change happen. The numbers that do get returned appear accurate. This may or may not include other changes in the same update, which I don't care about checking.
My calculated attribute is Update to Pending/On-Hold.
Then I am plugging that into a calculated metric:
I suspect there is something off about the DATE_FIRST_FIX and have tried changing the values there but most combinations lead to nothing at all being returned. Is something causing it to be unable to detect the first change in some cases?
1
Gabby H
I'm trying to accomplish the same as Jordan and keep getting blank results for tickets that were changed to status "Pending".
0
Ulises
Hi Eugene Orman,
I followed the steps to show the last agent update, and just like Colin Hutzan, I ran into an issue where there were a lot of Null or blank values. I checked some of those tickets and confirmed that agents had replied to all the tickets that I checked.
I am wondering if any of the following points have some kind of impact?
0
Permanently deleted user
can someone help me how to formula:
"Ticket First Solved - Date" ?
I need to create an attribute like that, thank you
0
Andrei Kamarouski
Hey @..., here you are
0
Permanently deleted user
hi Andrei Kamarouski thank you for the formula
-----------
but, is the formula is only for timestamp?
how if i wanted to use only date?
i want to get this value ==> 2023-10-19
not this ==> 2023-10-17T12:48:20
0
Andrei Kamarouski
Then use this
instead of this
0
Francis Casino
I'd like to express my agreement with Andrei's input. If you have any additional questions or if there are further concerns that you'd like to discuss, please don't hesitate to reach out. We're here to assist and provide you with any additional information or support that you may require. Your feedback and inquiries are highly valued, and we're committed to ensuring that you receive the best assistance possible.
Thank you for your engagement, and we look forward to addressing any further queries you might have.
0
Chad Mitrado
I need help in fixing this:
IF ([Changes - Field name]="assignee_id" AND [Changes - Previous value]=NULL
AND [Changes - New value]!="0" AND [Changes - New value]!=NULL
AND DATE_FIRST_FIX([Update - Timestamp],[Update ticket ID],[Changes - Field name])=[Update - Timestamp])
THEN [Update ticket assignee]
ENDIF
I tried to use the suggestion from this page: https://support.zendesk.com/hc/en-us/articles/4408845631258-Explore-recipe-Finding-the-first-assignee-for-a-ticket, but there's an issue. Every time the system updates the ticket and the “Assignee” becomes NULL, the next assignee is treated as the FIRST, so I added the Date_First_Fix.
The problem now is the NULL values. There are a lot of tickets going to the NULL values even if they were assigned to agents.
I need to count the number of tickets wherein the agent is the FIRST assignee regardless of how many agents handled the ticket.
0