For the original conversation, see Explore: Last refresh timestamp.
In Explore, editors and admins can see when a dataset was last refreshed by looking in the upper left corner of the report builder. However, dashboard viewers can't see this information, so by default they don't know when the data they're looking at was last refreshed.
In this recipe, you'll learn how to create a report that displays the approximate timestamp of the last refresh by returning the time of the last ticket update. You'll then add it to a dashboard for easy access by viewers.
This article contains the following topics:
- What you'll need
- Creating the last ticket updated metric
- Creating a Top/bottom attribute
- Configuring the chart
- Creating the dashboard
What you'll need
Skill level: Intermediate
Time required: 10 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket and agent data in Zendesk Support
Creating the last ticket updated metric
This metric returns the most recent time tickets were last updated. A display format is then used to append the result with the text " Minutes Ago".
To create the metric
- In Zendesk Explore, click the reports () icon.
- In the Reports library, click New report.
- On the Select a dataset page, choose Support > Support - Tickets (the dataset containing the ticket data you'll need to build the report).
- Click Start report. The report builder opens.
- In the Calculations menu (), click Standard calculated metric.
On the Standard calculated metric page, enter the following formula:
DATE_DIFF(now(),[Ticket updated - Timestamp],"nb_of_minutes")
- Name the metric Last Ticket Updated Mins, and then click Save.
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
- In the Metrics panel, click Add.
- From the list of metrics, choose Calculated metrics > Last Ticket Updated Mins (the metric you just created).
- In the Metrics panel, click the Last Ticket Updated Mins metric you just added.
- On the metric's page, click the pen icon to edit the metric settings.
- On the Standard calculated metric page, click Options > Edit aggregators.
- Clear all visible aggregators except for minimum (MIN), and then click Save. Explore displays the most recent time tickets were last updated.
- On the Standard calculated metric page, click Options > Edit display format.
- On the Display format page, click the drop-down list, then choose Custom.
- If the Suffix field, enter " Minutes ago" (with a leading space before the text).
- When you are finished, click Save.
Creating a Top/bottom attribute
This attribute displays only the lowest last ticket update time by using a Top/bottom attribute.
To create the attribute
- In the Calculations menu (), click Top/bottom attribute.
On the Top/bottom calculations page, configure the following:
Name: Last Sync Timestamp
For the metric: MIN > Last Ticket Updated Mins
keep the top: 0 and bottom 1
values of the attribute: Ticket Updated - Timestamp
Check Compute according to the filters in the report.
- When you are finished, click Save.
- In the Rows panel, click Add.
- From the list of attributes, choose Calculated attributes > Last Sync Timestamp, then click Apply.
Explore displays a table showing the last sync timestamp and the amount of time in minutes since a ticket was last updated.
Configuring the chart
In this section, you'll finish off the chart by removing the table headers, removing the display of any filters, and adding a date filter to restrict the results to only tickets updated in the past week.
To configure the chart
- In the Chart configuration menu (), click Chart.
- On the Chart page, make sure that Header visibility is not active. This hides the table headers in your report.
- On the Applied filters page of the Chart configuration menu, uncheck Show applied filters.
- In the Filters panel of the report builder, click Add.
- From the list of available filters, choose Time - Ticket last updated > Ticket updated - Date.
- Click the filter you just added. On the Ticket updated - Date page, click Edit date ranges.
- On the Simple tab of the Date range page, click This week, then click Apply.
Explore displays the final report:
- Enter a name for the report, then click Save.
Creating the dashboard
You can now add your report to a dashboard. For help, see Adding and arranging dashboard widgets.
If your dashboard contains any filters, select your report, and from the menu, click Exclude filters.
It looks like this unfortunately doesn't work anymore. It says this queries too many rows, and snips the data, so the timestamp is shows is way off (like, it showed me a timestamp with the year 2013).
Edit: The easy fix: For most folks, add in a "Ticket Updated - Date" filter set for "Today". You might get weird results for midnight runs, or if your data updates daily instead of hourly, but otherwise should avoid data overload and getting a wildly wrong date.
Apologies for the delay here. I have created a ticket on your behalf so we can look into your query further.
It does appear to no longer work. Even with the steps CJ added its still off. Right now the data set is 34 minutes last update, this formula is showing 86 minutes.
Apologies for the delayed response. In order for us to properly investigate this, I've created a ticket on your behalf to look into this further.
Hi all, I just wanted to post a quick note to clarify how this recipe works, in light of a few of the comments above:
So while this recipe isn't an exact replacement for the dataset sync timestamp, it's still a useful way for dashboard viewers to know approximately how current the info on a dashboard is. Hope that helps!
I am having the same issues. This recipe isn't working for me and is showing a timestamp from 2019. I'd love some help or a resolution.
Hi CX Automations! If you're seeing a timestamp from 2019, a quick thing to check is whether the Top/bottom attribute is set correctly. You'll want to make sure the top field is set to 0 and the bottom field is set to 1. (If those two fields are switched, you'll see the oldest timestamp instead of the newest.)
All of the images were removed from this article, makes it rather tricky to tell what it is instructing.
Hi CJ Johnson, I've restored all the images in the article—sorry for the confusion!
The article image has MED for the metric, but the instructions have you only allow MIN. Which is the correct setting?
Hi Monica, the correct setting should be MIN. I've updated the screenshot so that it matches the instructions!
Why don't you just expose the value for use so it can be used so there is not need to write an convoluted process that actually returns an inaccurate value?
Question: Whats the value of the Last ticket updated Mins represent. I thought it would change as time progressed as it calculates against now?
Dave Symonds It just lets you know how out-of-date the dashboard is, if you have a plan where it updates once an hour, it's not on the hour, so it can be hard when looking at a Dashboard to tell when it last pulled in fresh data.
Please sign in to leave a comment.