For the original conversation, see Explore: Last refresh timestamp.
In Explore, you can't currently find out when a dataset was last refreshed with the latest information. However, you can estimate this by querying when the last ticket update took place.
In this recipe, you'll learn how to create a query that displays the estimated timestamp. 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 median time since 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 query () icon.
- In the Queries library, click New query.
- On the Choose a dataset page, choose Support > Tickets > Support: Tickets [default] (the dataset containing the ticket data you'll need to build the query).
- Click New query. Query 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 median (MED), and then click Save.
Explore displays the median time in which 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 query.
- 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 query.
- On the Applied filters page of the Chart configuration menu, uncheck Show applied filters.
- In the Filters panel of Query 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 query:
- Enter a name for the query, then click Save.
Creating the dashboard
You can now add your query to a dashboard. For help, see Adding and arranging dashboard widgets.
If your dashboard contains any filters, select your query, and from the menu, click Exclude filters.