Explore enables you to filter a reports by business hours. To do this, you need to create a calculated attribute based on the correspondingDay of weekandHourattributes. 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 (seeWorking with datasets).
What you'll need
技术水平:Intermediate
Time required:30 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (seeGiving agents access to Explore)
Creating the report
To show tickets created during business hours
- In Explore, click the reports (
) icon. - In the Reports library, clickNew report.
- On theSelect a datasetpage, click支持>支持-Tickets,然后单击Start report.The report builder opens.
- In the Calculations menu (
), clickStandard calculated attribute. - On theStandard calculated attributepage, give your attribute a name likeCreated in business hours.
- In theFormulafield, 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
ENDIFIn 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 articleto help you enter Explore formulas in your language. - ClickSave. Now, you'll create a report using this attribute.
- In theMetricspanel, clickAdd.
- From the list of metrics, chooseTickets>Tickets.
- In theColumnspanel, click Add.
- 从列表中选择的属性Calculated attributes>Created in business hours(the attribute you just created).
- Click theVisualization typemenu (
) and selectPie.
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","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:30-10:59", "11," "12", "13"))))
THEN TRUE
ELSE FALSE
ENDIF
9 Comments
我有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.
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!
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!
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"))).
Pleasesign into leave a comment.