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))) 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. - 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 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
15 comments
Greytip Software Pvt Ltd
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.
0
Gab Guinto
Hi Vijayendra,
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.
Thanks Vijayendra!
1
Jonathan Lindsay
Hello,
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!
0
Dainne Kiara Lucena-Laxamana
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.
0
CJ Johnson
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?
2
Andy Verner
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?
1
James Pellizzi
Is there a way to track Outside of Business Hours?
1
Gab Guinto
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.
0
Gab Guinto
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"))).
0
Sierra Collins
Is there a way to calulate how long it took to escalate a ticket to another team, but only count the hours within business hours? I have a formula to calcluate the timestamp the ticket was escalated from the ticket assignement.
DATE_DIFF([Configuration Triage Timestamp],[Ticket first assigned - Timestamp], "nb_of_hours")
The issue doesnt exlude weekend or any time after business hours.
0
Dainne Kiara Lucena-Laxamana
Hi Sierra!
You could incorporate the formula stated in the article. Specifically the one specifying which time of the day is considered within the business hours:
Hope this helps!
0
Vaibhav Shetty
is there a way to calculate how long it took to solve the ticket after it got created, we have created a custom attribute to calculate the days, but we want to exclude the Saturday and Sunday from the calculated days can someone help me on the below formula
DATE_DIFF([Ticket solved - Date],[Ticket created - Date],"nb_of_days"). please anyone help me on this
0
Almog Zamir
Hey there!
We have multiple Schedules, which means we have different business hours (several time zones). How does this recipe support our need to calculate metrics only within business hours?
0
Alex Zheng
Only one schedule can be applied to a ticket at a time so depending on which schedule is applied to the ticket will be the business hours used in the calculations.
0
TINA YATES REALMT
I'm trying to find AI bot engagement outside of business hours however I am not able to get a result with the following,
IF ([Activity event type] = "user_input_received")
IF (IN([Time - Activity occurred - Day of week],ARRAY("Monday", "Tuesday","Wednesday","Thursday","Friday")))
AND (IN([Time - Activity occurred - Hour], ARRAY(0, 1, 2, 3, 4, 5, 6, 7, 8,18,19,20, 21, 22, 23)))
OR ([Time - Activity occurred - Day of week], ARRAY("Saturday", "Sunday")
THEN [Sunshine Conversations app user ID] ELSE NULL ENDIF
Any ideas what I am missing to get this to work?
0