Explore recipe: Creating weekly date buckets

Return to top
Have more questions? Submit a request

17 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

    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

    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

Please sign in to leave a comment.

Powered by Zendesk