Explore enable 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 for slicing or filtering your report.
In the example 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 calls received during business hours. Make sure to choose the dataset containing the metrics and attributes you need (see Working with datasets).
To show tickets created during business hours
- Open a new query using the Tickets dataset.
- 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 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 - Click Save. Now, you'll create a report using this attribute.
- In the Metrics panel, add the metric COUNT(Tickets).
- In the Columns panel, add the attribute you just have 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
2 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:
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", "15")))
OR([Ticket created - Day of week]="Sunday" AND IN([Ticket created - Hour],ARRAY("10", "11", "12", "13", "14"))))
THEN TRUE
ELSE FALSE
ENDIF
To check if it returns reasonable results, you can run this query:
Please sign in to leave a comment.