English (US)
  1. Zendesk help
  2. Explore
  3. Using Explore for reporting and analytics
  4. Explore recipes

Explore recipe: Filtering queries by business hours

Eugene Orman
  • Edited
Zendesk Product Manager

Suite Professional, Enterprise, and Enterprise PlusExplore Professional only

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

Creating the query

To show tickets created during business hours

  1. In Explore, click the query () icon.
  2. In the Queries library, click New query.
  3. On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query. Query builder opens.
  4. In the calculations menu (), click Standard calculated attribute.
  5. On the Standard calculated attribute page, give your attribute a name like Created in business hours.
  6. 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
    Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language.
  7. Click Save. Now, you'll create a report using this attribute.
  8. In the Metrics panel, click Add.
  9. From the list of metrics, choose Tickets > Tickets.
  10. In the Columns panel, click Add.
  11. 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
Return to top
Have more questions? Submit a request

9 Comments

  • James Molina

    This is great!  Can you explain how the formula would be updated if you had different business hours for Saturday and different for Sunday?

    0
  • Eugene Orman
    Zendesk Product Manager

    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:

    0
  • Kristin Bouveng

    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? 

     

    0
  • Eugene Orman
    Zendesk Product Manager

    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. 

    0
  • Lucas Cumming

    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.

    0
  • Emma Löfström

    Hi Lucas, I managed to do it for Talk. 

     

    IF ([Call direction]="Inbound")
    AND (IN([Call - Day of week], ARRAY("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")) AND IN([Call - Hour], ARRAY("9", "10", "11", "12", "13", "14"))) 
    THEN [Call ID]
    ELSE FALSE
    ENDIF
    0
  • Emma Löfström

    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?

    "IF ([Call direction]="Inbound")
    AND ([Call completion status]="Completed")
    AND (IN([Call - Day of week],ARRAY("Monday", "Tuesday","Wednesday","Thursday","Friday"))
    AND IN([Call - Hour],ARRAY("9", "10","11","12","13", "14")))
    THEN [Call ID] 
    ELSE FALSE
    ENDIF"

     

    1
  • Vijayendra

    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
    • Edited
    Zendesk Customer Advocate

    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!

    0

Please sign in to leave a comment.

Related articles

Powered by Zendesk