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 query 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 query 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 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 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 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.