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).
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
- In Explore, click the reports () icon.
- In the Reports library, click New report.
- On the Select a dataset page, click Support > Support - Tickets, then click Start report. The report 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")))
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.
- 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).
- 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:30 AM and 2 PM, then use this formula:
IF (IN([Ticket created - Day of week],ARRAY("Monday", "Tuesday","Wednesday","
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:30-10:59", "11," "12", "13"))))
I have the same issue as Emma. Anyone figured this out yet? Zendesk team-Please advise. We didn't have to do this in Insights as we had set the working hours in the schedule. How can we get this working in Explore? We report out metrics like response and resolution time in business hours which in our case is 9:30 am to 6:30 pm.
If you have schedules set on your account, then your Explore should have both calendar and business hour metrics for First reply time and Full resolution time. You don't have to build custom metrics or attributes to measure these based on business hours; just select the business hours metrics when building your queries.
I have tried to create a custom business hours metric, however when going to add it, there is not an attribute called "Calculated attributes" for me to select from.
When trying to create the same metric, it says that the metric already exists implying that I have saved it correctly.
Am I missing something here? Why wouldn't the "Calculated attributes" appear?
Thank you in advance for your help!
Hi Jonathan! You mentioned you created a custom metric. If you did create a metric then that is expected not to appear under Rows or Columns as an attribute. Custom metrics would only appear under Metrics. If you want to add that calculated attribute under Rows or Columns, then you'd need to create a Standard calculated attribute.
It might be good to note that this only works if everyone who uses and loads the query is in the same timezone. On that note, is there any way to make this formula use a specific timezone, so that when I look at the report, and my coworker in France looks at the report, we see the report showing Monday-Friday 9-5 for France for both of us?
Similar to the default values for full resolve time, how can I create a custom metric that only counts business hours? I.e. I want to count all hours tickets were in an 'open' status for the last month, I can see it in calendar hours, but I want to see this in business hours. What's the best way to achieve this?
Is there a way to track Outside of Business Hours?
I'm afraid this is not possible. Only a few of the default metrics (like First reply time, Requester wait time, etc.) under the Tickets dataset are available in biz hours. The status time metrics under the Updates history dataset are only available in calendar hours. Unfortunately, we also do not have any published recipe or a user recommended workaround to calculate these metrics in business hours through custom metrics.
In the recipe above, the business hours are defined in the attribute formula. If you need to look for tickets created outside business hours, then you can filter the report using the custom attribute and select 'FALSE' under Selected.
Alternatively, you can build your formula in such a way that TRUE points to the hours outside of your schedule; example, if your business hours is Monday to Friday, from 9 am to 5 pm, then you can formulate the condition as (IN([Ticket created - Day of week],ARRAY("Saturday", "Sunday"))) and (IN([Ticket created - Hour],ARRAY("0", "1","2","3","4","5","6","7","8","17","18","19","20","21","22","23"))).
Please sign in to leave a comment.