Explore enables you to filter a query 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 9am and 5pm. 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).
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)
- Zendesk Guide with Answer Bot configured
Creating the query
To show tickets created during business hours
- In Explore, click the query (
) icon.
- In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query. Query builder opens.
- In the calculations menu (
), click Standard calculated attribute.
- On the Standard calculated attribute page, give your attribute a name like Created in business hours.
- 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
ENDIFTip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - Click Save. Now, you'll create a report using this attribute.
- In the Metrics panel, click Add.
- From the list of metrics, choose Tickets > Tickets.
- In the Columns panel, click Add.
- From the list of attributes, choose Calculated attributes > Created in business hours (the attribute you just created).
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 9am and 5pm, Saturday between 9am and 3pm, Sunday between 11am and 2pm 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(11", "12", "13"))))
THEN TRUE
ELSE FALSE
ENDIF
7 Comments
This is great! Can you explain how the formula would be updated if you had different business hours for Saturday and different for Sunday?
That's a good question, James.
You will need to add two more OR conditions. Here is a formula examle:
To check if it returns reasonable results, you can run this query:

Hi,
I have reports by hour on various datasets and metrics, but the query isn't displaying the hours in chronological order (see below screenshot).
How can I change that?
Kristin Bouveng, hours are normally sorted chronologically unless you have some Result manipulation applied in your query. Check if you have some sort of sorting enabled set in this query. If the issue will persist please reach out to our support team.
Hey Eugene, I am glad you've figured out how to report this for tickets. I am struggling to see if there is a way to do this for calls through Zendesk Talk. Have you figured out a way to do this? Either way, I appreciate it.
Hi Lucas, I managed to do it for Talk.
Hi again, One of our phone lines opens at 09:30. How do I adjust this?
I have used following code which works works perfect, HOWEVER I am not able to set it for the line which opens at 09:30. I have tried to enter 9:30 instead in the code, but then the result will only contain calls from 10 and forward. I do not have to use [Calls - hour] of course, but I have not found another way to do it. Any suggestions?
Please sign in to leave a comment.