Explore recipe: Creating a last refresh timestamp

What's my plan?

 Contributed by community moderator Graeme Carmichael.
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 standard calculated 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

1. In Zendesk Explore, click the reports () icon.
2. In the Reports library, click New report.
3. On the Select a dataset page, choose Support > Support - Tickets (the dataset containing the ticket data you'll need to build the report).
4. Click Start report. The report builder opens.
5. In the Calculations menu (), click Standard calculated metric.
6. On the Standard calculated metric page, enter the following formula:

   DATE_DIFF(now(),[Ticket updated - Timestamp],"nb_of_minutes")

7. 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.
8. In the Metrics panel, click Add.
9. From the list of metrics, choose Calculated metrics > Last Ticket Updated Mins (the metric you just created).
10. In the Metrics panel, click the Last Ticket Updated Mins metric you just added.
11. On the metric's page, click the pen icon to edit the metric settings.
12. On the Standard calculated metric page, click Options > Edit aggregators.
13. Clear all visible aggregators except for minimum (MIN), and then click Save. Explore displays the most recent time tickets were last updated.
14. On the Standard calculated metric page, click Options > Edit display format.
15. On the Display format page, click the drop-down list, then choose Custom.
16. If the Suffix field, enter " Minutes ago" (with a leading space before the text).
17. When you are finished, click Save.

Creating a standard calculated attribute

This attribute displays only the lowest last ticket update time by using a standard calculated attribute.

To create the attribute

1. In the Calculations menu (), click Standard calculated attribute.
2. In the Standard calculated attribute panel, configure the following:
   
   • Name: Last Sync Timestamp
     
   • Formula: DATE_LAST([Ticket updated - Timestamp])
     
3. Click Save.
4. In the Rows panel, click Add.
5. 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

1. In the Chart configuration menu (), click Chart.
2. On the Chart page, make sure that Header visibility is not active. This hides the table headers in your report.
   
3. On the Applied filters page of the Chart configuration menu, uncheck Show applied filters.
   
4. In the Filters panel of the report builder, click Add.
5. From the list of available filters, choose Time - Ticket last updated > Ticket updated - Date.
6. Click the filter you just added. On the Ticket updated - Date page, click Edit date ranges.
7. On the Simple tab of the Date range page, click This week, then click Apply.
   Explore displays the final report:
   
8. 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.