English (US)
  1. Zendesk help
  2. Explore
  3. Using Explore
  4. Explore recipes

Explore recipe: Creating weekly date buckets

Andrew Forbes
  • Edited

Explore Professional only

This recipe shows you how to display your weekly data in date buckets rather than just showing the individual week number.

What you'll need

Skill level: Medium

Time required: 20 mins

Create your calculated attribute

To start, you will need to create a standard calculated attribute (see Creating standard calculated attributes).

To create your date buckets calculated attribute

    1. In Zendesk Explore, click the query () icon.
    2. In the Queries library, click New query.
    3. On the Choose a dataset page, click Support > Ticket updates > Support: Tickets, then click New query. Query builder opens.
    4. In your query, open the Calculations menu (), then click Standard calculated attribute. 
    5. On the Standard calculated attribute page, under Name, enter a name for the attribute, like Week Bucket. You can change this name later if you want.  
    6. In the Formula field, enter or paste the formula below: 
      DATE_FORMAT(START_OF_WEEK_MONDAY([DATE(Date)]), 
      "YYYY-MM-dd") + " " + "to" + " " + 
      DATE_FORMAT(END_OF_WEEK_MONDAY([DATE(Date)]), "YYYY-MM-dd")

      Note: You need to update [DATE(Date)] to the name of the date attribute you would like to use like Ticket Solved - Date, Ticket First Assigned - Date, etc.

      For example, using Ticket Solved - Date, replace [DATE(Date)] with [Ticket Solved - Date].
      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 to create the calculated attribute. . 

Display your results

After you created your calculated attribute, you can add it and any other data to your query. When using the calculated attribute, rather than a pre-built week attribute, your results will display like the image below:

Alternatively, if you’re only interested in seeing the start of the week rather than the full duration, you can edit the formula and remove + " " + "to" + " " + DATE_FORMAT(END_OF_WEEK_MONDAY([DATE(Date)]), "YYYY-MM-dd").

The complete formula would look like below: 

DATE_FORMAT(START_OF_WEEK_MONDAY([DATE(Date)]), "YYYY-MM-dd")
Return to top
Have more questions? Submit a request

19 Comments

  • Terry O'Donnell

    Hi Andrew,  This article was helpful.  I have a need for all Explore Weekly Reporting to start Sunday and end on Saturday.  The current default Week in Explore starts on Monday and ends on Sunday.

    Will I need to build out some weekly metrics to have a Sun-Sat work week?

     

    Insights allowed a couple choices of week start and end dates.

     

    Can the be a feature Request for Explore?

     

    Thanks, ~Terry (Evernote)

    1
  • Andrew Forbes

    Hey Terry,

     

    You can build out some custom metrics to track weeks that start on Sunday. The formula looks something like: START_OF_WEEK_SUNDAY(_date). 

     

    We don't currently have it on the roadmap for this to be a native feature, but it is possible with a Calculated Attribute. I'll bring it up with our Product team to see their thoughts! 

    0
  • Travis Tubbs

    I think the syntax of the example provided is incorrect because I keep getting an error when copying it and attempting to put in a field name. Specifically, the brackets are in the wrong place.

    The field name should be enclosed in brackets, not the DATE function. For example:

    DATE_FORMAT(START_OF_WEEK_MONDAY(DATE([Date])), "YYYY-MM-dd")

    At least, that's what I had to do to get this to work.

    0
  • Nicole Haberman
    • Edited

    I keep getting an error that is difficult to track down "An error has occurred while evaluating Week Bucket for the following values: Ticket solved - Date -> ." 


    I have the following as my calculated query: 

    DATE_FORMAT(START_OF_WEEK_MONDAY([Ticket solved - Date]), "YYYY-MM-dd")
     + " " + "to" + " " + DATE_FORMAT(END_OF_WEEK_MONDAY([Ticket solved - Date]),
     "YYYY-MM-dd")

     

    Is there something wrong with the syntax of the query I have, or is the error related to something else? 

    0
  • Rob Stack
    Zendesk Documentation Team

    Hi Nicole, I tried this query and got the same error as you did. I'm going to create a ticket for this so we can investigate it properly and get this working for you. Someone will be in touch soon.

    0
  • Allen Lai

    Feels like something that should come out of the box, rather than having to create a custom calculated attribute.

    3
  • Devan - Community Manager
    Zendesk Community Team

    Hello Allen Lai,

    I appreciate the insight on Explore. I would suggest posting your thoughts in our product feedback forums so our developers can consider it for a future update in the future.

    Best regards. 

    0
  • Stanley Franco

    Hey Devan - Community Manager is there a Zoom or Hangouts for the AMA tomorrow? How do I sign up?

    0
  • Ellen Livengood

    I had this working fine for the last year or so, recently it has stopped working. I am using this syntax:

    DATE_FORMAT(START_OF_WEEK_MONDAY([Ticket created - Date]), "YYYY-MM-dd")

    If I apply the generic "Ticket created - week of year" attribute to my query (instead of my custom Weekly Bucket attribute), it calculates and displays the results just fine, however the weekly labels are "1,2,3,4,...etc". All I want to do is translate "1,2,3, etc" to "2020-01-01, etc".

    Seems like there should be a way to do this without creating a custom attribute, but given that I cannot seem to do this can someone check my syntax?

    0
  • Nicole Haberman

    This query has recently stopped working for me as well Ellen, I have a support case open, but have not heard anything back. Hopefully, it is something that can be fixed easily, or something can replace it, I need these reports for management. 

    0
  • Rob Stack
    Zendesk Documentation Team

    Hi Ellen and Nicole. I've opened a ticket about this so we can investigate what's going on here. Based on the findings, I'll make any necessary doc updates. Thanks!

    0
  • Florian W.

    This recipe worked well for me, but I'd be interested to see the weeks as e.g CW01, CW02,...

    Does anyone know how to change the code to the calendarweeks instead of the dates?

    0
  • Bart

    I have the exact problem except that the week starts on a Sunday. 

    Is it possible to adjust reporting for your start day (Monday - Sunday)?

    As you can see, the start date for viewing this week is based on the 14th - the 20th.

    Secondly, is there also a way to use regular date formating, like 31/12/2000?

     

    0
  • Rob Stack
    Zendesk Documentation Team

    Hi Bart, there are a bunch of different START_OF_WEEK functions as detailed in https://support.zendesk.com/hc/en-us/articles/360022184834 including START_OF_WEEK_SUNDAY. Might this help you?

    0
  • Jiri Kanicky
    • Edited

    What would be the formula for Created vs Resolved query?

    I need to display the data weekly.

    The following formula is not working, because the ticket numbers displayed are incorrect (do not add up)

    DATE_FORMAT(START_OF_WEEK_MONDAY([Ticket updated - Date]),
    "YYYY-MM-dd") + " " + "to" + " " +
    DATE_FORMAT(END_OF_WEEK_MONDAY([Ticket updated - Date]), "YYYY-MM-dd")

     

    0
  • Devan - Community Manager
    Zendesk Community Team

    Hello Jiri,

    To accomplish this, you will need to make two reports and make some changes in your formula. \

     

    1. For report one, you will need to change Ticket updated to Ticket created.

    2. For report two, you will need to change Ticket updated to Ticket resolved. 

     

    Once you implement these changes, you should be able to run your reports will no issue.

    Best regards. 

    0
  • Jiri Kanicky

    Hi Devan.

    I was able to find the solution on the weekly, but we switched to EazyBI. This is feature is out of the box there and EazyBI is much better reporting tool in comparison to Explore.

    Thanks.

    0
  • Jesse Marion

    Hi this is great thank you for sharing, I am trying to replicate the same scenario except with ticket updates dataset for public comments made each week, however, I get errors using D count public comments and when using just comment public I don't get WoW results

     

    DATE_FORMAT(START_OF_WEEK_MONDAY([Comment public]),
    "YYYY-MM-dd") + " " + "to" + " " +
    DATE_FORMAT(END_OF_WEEK_MONDAY([Comment public]), "YYYY-MM-dd")

     

    and tried

     

    DATE_FORMAT(START_OF_WEEK_MONDAY([D_COUNT(Public comments)]),
    "YYYY-MM-dd") + " " + "to" + " " +
    DATE_FORMAT(END_OF_WEEK_MONDAY([D_COUNT(Public comments)]), "YYYY-MM-dd")

     

    Any suggestions? Thank you

    0
  • Marie-Cathrine Sørensen
    • Edited
    Community Moderator

    Hi Jesse Marion,

    The issue is that you are trying to put in a metric instead of a date attribute. Try this instead:

    1. Create a date bucket attribute using this formula (based on the above guide):  
      DATE_FORMAT(START_OF_WEEK_MONDAY( [Update - Date]), 
      "YYYY-MM-dd") + " " + "to" + " " + 
      DATE_FORMAT(END_OF_WEEK_MONDAY( [Update - Date]), "YYYY-MM-dd")
    2. Add it as an attribute in Rows.
    3. In metrics, select D_COUNT(Public comments).

    I hope this will give you the result, that you are looking for. 

    #helpsome regards,
    Marie-Cathrine Sørensen
    Business Intelligence Specialist & Junior Developer @ helphouse.io

    0

Please sign in to leave a comment.

Related articles

Powered by Zendesk