Explore recipe: Filtering reports by business hours

What's my plan?

Explore enables you to filter a reports by business hours. To do this, you need to create a calculated attribute based on the corresponding Day of week and Hour attributes. Then use it to slice or filter your report.

In the example recipe below, the goal is to see the number of tickets created during business hours from Monday to Friday between 9 AM and 5 PM. You can use the same logic to look at the number of chats or Talk calls received during business hours. Make sure to choose the dataset containing the metrics and attributes you need (see Working with datasets). 

Note: For the most accurate results, ensure the timezones used match the user that is creating the report and those that will view it. Since Explore uses the user's timezone when viewing reports, you'll need a calculated attribute to show results for each timezone. For more information, see: Which time zone does Zendesk use? 

What you'll need

Skill level: Intermediate

Time required: 30 minutes

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

Creating the report

To show tickets created during business hours

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. The report builder opens.
4. In the Calculations menu (), click Standard calculated attribute.
5. On the Standard calculated attribute page, give your attribute a name like Created in business hours.
6. In the Formula field, enter or paste the following formula. 

   IF (IN([Ticket created - Day of week],ARRAY("Monday", "Tuesday","Wednesday","Thursday","Friday"))
       AND IN([Ticket created - Hour],ARRAY(9, 10, 11, 12, 13, 14, 15, 16)))
   THEN TRUE
   ELSE FALSE
   ENDIF

   In the second ARRAY function above, the numbers represent the whole hour. In other words, "9" means it captures any ticket created between 9:00 through 9:59.

   Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
7. Click Save. Now, you'll create a report using this attribute.
8. In the Metrics panel, click Add.
9. From the list of metrics, choose Tickets > Tickets.
10. In the Columns panel, click Add.
11. From the list of attributes, choose Calculated attributes > Created in business hours (the attribute you just created).
12. Click the Visualization type menu () and select Pie.

If your business hours vary depending on the day of the week, you can add additional conditions to the calculated attribute's formula. For example, if your business hours are Monday to Friday between 9 AM and 5 PM, Saturday between 9 AM and 3 PM, Sunday between 10 AM and 2 PM, then use this formula: 

IF (IN([Ticket created - Day of week],ARRAY("Monday", "Tuesday","Wednesday","Thursday","Friday")))
AND (IN([Ticket created - Hour], ARRAY(9, 10, 11, 12, 13, 14, 15, 16)))
OR([Ticket created - Day of week]="Saturday" AND (IN([Ticket created - Hour], ARRAY(9, 10, 11, 12, 13, 14))))
OR([Ticket created - Day of week]="Sunday" AND (IN([Ticket created - Hour], ARRAY(10, 11, 12, 13))))
THEN TRUE
ELSE FALSE
ENDIF