In this Explore recipe, you'll learn how to create a report that shows you when an organization last submitted a ticket.
What you'll need
Skill level: Moderate
Time Required: 20 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the report
To create the report
- In Zendesk Explore, click the reports () icon.
- In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Tickets, then click Start report. The report builder opens.
- Now, you'll create a Standard calculated metric to show the number of days since a ticket was created and today's date. Click Calculations (), then click Standard calculated metric.
- On the Standard calculated metric page, enter a name for the metric like Number of days between today and date ticket created.
- In the Formula field, enter or paste the following formula:
DATE_DIFF(TODAY(),[Ticket created - Timestamp],"nb_of_days")You'll end up with a page that looks similar to the following.
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
- When you are finished, click Save.
- In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > Number of days between today and date ticket created, the custom metric you just created, then click Apply.
- Ensure the metric aggregator for Number of days between today and date ticket created is set to SUM. For more information, see Choosing metric aggregators.
- In the Rows panel, click Add.
- From the list of attributes, choose Requester organization > Requester organization name, then click Apply.
- (Optional) Click the Requester organization name attribute and exclude NULL values. This will exclude results where the requester does not belong to an organization.
- From the list of attributes, expand Ticket, click Ticket ID, then click Apply.
- Click Result manipulation () then Top/bottom. Configure the following options:
- Bottom: 1 result
- Metric: SUM(Number of days between today and date ticket created)
- Strategy: Per block on rows
Then the report is complete. See the screenshot below as an example.
Accurate to say you could use a similar metric to calculate days since the ticket was last updated?
DATE_DIFF(TODAY(),[Ticket updated - Date],"nb_of_days")
What I'm specifically trying to calculated is days since the ticket last received a public comment - any suggestions?
I'm afraid at this time this currently isn't possible using just "[Ticket updated - Date]" as this will also be reflective of ticket updates that don't include a public comment.
Once we release our MIN/MAX(date) function though; this should enable the ability to build a custom attribute that incorporates the [Update - Date] to calculate the latest ticket update date based on public comment.
Whilst I'm unable to confirm an ETA it shouldn't be too far away but hope this helps to clarify your question at this immediate moment!
@... any updates on MIN/MAX(date) function ?
At this time, we do not have any new information or announcements on this product update or its release date. However, I would recommend subscribing to the announcements section of the community, where most product releases are published to stay up to date on releases like this and more.
We actually just released this functionality recently. You can learn more about how these new functions here.
@... Thanks, but why is this not in any release notes or announcements? @... 's suggestion to follow announcements means that anyone who is waiting on this feature still doesn't know.
@... That new explore functionality is covered in our July What's New. You can subscribe to the What's New section if you're like. We try to thoroughly cover all product updates there.
Not all products are covered in Release Notes (though almost all are) and not every new feature gets it's own announcement.
That article was posted 24 minutes ago, after my comment, so this is a pretty disingenuous reply.
@... When you all are closing out all our bug tickets and telling us to watch Release notes from now on for updates, and then there isn't any release notes, and the feature already has been released, that sows distrust and frustration. Additionally, subscribing to Release Notes or Announcements is not the same as subscribing to What's New, so the directions that are being handed out on the bugs you are closing, are incorrect and would lead your customers to missing the updates because you didn't tell them there's actually two places to look.
Hi @..., the What's New was published yesterday, on the 1st of the month. It was edited this morning because we realized a couple of updates made (prior to publishing) were overwritten, so we added them back into the published version today.
I am not responsible for the release notes or closing your tickets. I just thought the What's New might be helpful to you for product updates.
Hey CJ -
We're working on improving the communications around feature requests, releases, etc. Your comment is good feedback on the fact that it is confusing not to have a single source of truth, and I'll raise this with the team responsible for working on those improvements.
We'll be announcing the first steps we're taking around product communication in the community at an event on July 13. You can register here to attend or get a link to the recording: https://events.zendesk.com/events/details/zendesk-expert-q-as-presents-zendesk-community-feedback-update/
I set the formula up and it works great. However, when I try to excluded days (under 5) the report brings back the old value versus the new. Example, I updated a case yesterday - with the filter on it shows 56 days - with the filter off it shows 1
Based on the information you provided it seems that you are using [Ticket updated - Date]. If that's the case, it will be better if you utilize the MIN/MAX(date) function.
If you are already using it and the same behavior persists, please contact our support directly so that we can investigate further.
Dane I am using the DATE_DIFF (Today(),[Update - Date], "nb_of_days") - however when I try to exclude anything under 5 it brings back the comment date from before that. I am trying to see which cases haven't been update in over 5 days. There are two calculated Metrics - see below. The first gets the time stamp of the last update and the second tells how many days since the last update. Could your provide an example of the MIN/MAX function mentioned above - the metric I am using doesn't have this
I have created a ticket for you to investigate. Please wait for my update via email and let's continue from there.
Dane I tried the Min/Max that someone recommended but it didn't work. I found an arictle that said to try to convert the value to a Number, which I did and now the filter works correctly. Thanks for your quick response
Please sign in to leave a comment.