Explore recipe: Using constants to add historical data to reports

What's my plan?

Suite

Professional, Enterprise, or Enterprise Plus

Support with

Explore Professional or Enterprise

The Explore team recently announced a 37-month data retention limit for Explore starting July 7, 2025, to improve report loading speeds. Although we've found that relatively few Explore customers need to produce reports for periods over 37 months, there are occasions when you might want to do this.

For example, here on the Zendesk documentation team, we produce a report that shows the total number of tickets solved each year:

However, in 2021, we redesigned our Zendesk instance, meaning data from before then was no longer available. We recreated the report using constant values because the ticket-solved data from 2021 and before will not change. 

In this recipe, you'll learn how to use constants to create a report containing information over 37 months old that Explore hasn't retained.

This recipe contains the following topics:

• What you'll need
• Gathering your data
• Creating your metrics
• Constructing the report

What you'll need

Skill level: Intermediate

Time required: 20 minutes

• Zendesk Explore Professional or Enterprise
• Editor or Admin permissions (see Giving agents access to Explore)

Gathering your data

Before creating the report, you'll need to gather the data you want to add to the report that Explore doesn't store. Ways to do this include:

• If you have a printout or screenshot of an older report version, keep it handy when entering the constant values.
• Remember that although Explore doesn't read data older than 37 months, the information is stored in Support. You could create a view containing the tickets from each year or use the Zendesk API to retrieve the information
• Export any reports that return year-over-year data before the 37-month data retention period is applied in Explore. See Exporting dashboard tabs and reports.

Creating your metrics

You need to create a standard calculated metric for each year you want to report on. 

To create the metrics

1. In Explore, click the reports ( ) icon.
2. In the Reports library, click New report.
3. On the Select a dataset page, click Support > Support - Tickets, then click Start report.
   Report builder opens.
   Next, for each year you want to report on, perform the following actions:
4. In your report, open the Calculations menu (), then click Standard calculated metric. 
5. On the Standard calculated metric page, under Name, enter a name for the metric, like 2023 tickets. You can change this name later if you want. In the Formula field, enter or paste one of the following formulas:
   For years that are within the 37-month retention period
   In this example, you'll return all tickets assigned in 2024

   IF [Ticket solved - Year]=2024 THEN [Ticket ID] ENDIF

   For years that are outside the 37-month retention period

   <number of tickets>

   For example, if you solved 35 tickets in 2017, enter 35 as the only value in the formula. Explore treats this as a constant value that won't change.

6. Click Save.
7. Continue creating standard calculated metrics for each year you need.

Building the report

Now that you've created your metrics, you can continue to build the final report.

To build the report

1. In the Metrics panel of your report, click Add.
2. From the list of metrics, expand the Calculated metrics section.
3. From the list of metrics, choose each of the metrics you created in the previous section, clicking Apply after each one.
   Make sure that the metrics you added are in year order, from earliest to latest. You can drag them into the correct order from the metrics panel.
4. Change the aggregator to D_COUNT for each calculated metric and MIN for each constant metric. This will ensure that multiple solves on the same ticket are not counted.
5. Click the Visualization type menu () and select Column.
6. From the Chart configuration menu ( ) , click Chart.
7. On the Chart page, turn off Stacked.

Your chart is displayed. You can click any column to see the number of tickets for each year.