How do I display the start date of the week instead of the week number?

Return to top

16 Comments

  • Aaron Henderson

    Hi there,

    Is it possible to use this formula to view ticket update week, by start of week? And for it to display based on individual updates and not the latest update? I used the below formula and am seeing some odd returns:

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

    I noticed some odd returns and the numbers not line up when I simply used "Update - Week of year". I added Ticket Update attributes on one card to see what would return. When I did that it returned the report below.

    I did not expect to see multiple weeks of "Update - Week of year" within each "Ticket Updated Week by Start of Week". I drilled in to spot check some tickets and found that my created attribute was displaying the last time the ticket was updated, whether it was by the updater I am filtering for or not. The "Update - Date" and "Update - Week of Year" however correctly show the date/week the ticket was updated by the updaters.

    Is this expected? Is there a way to display ticket update week by start of week instead of just week number? And it show based on individual updates and not the latest update?

     

    Thanks,

    Aaron

    0
  • Kate L

    Hi Aaron,

    Thanks for sharing your use case. Looking at your formula, it is likely expected to get multiple weeks since you are using the Ticket Updated - Date attribute. This attribute would show a collection of attributes that return the time a ticket was last updated in various time measurements. You may want to use the Time - Ticket update attribute instead so in that case, your formula should be like this:

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

    This list of metrics and attributes will give you an idea of how are they are being calculated and defined across different Support datasets. Hope this helps.

    0
  • McCabe Tonna (Bot)

    To help future Explorers out, if you're looking at the other Dataset "ticket updates" 

    It depends what data you're looking at:

    In my query, I'm looking at Tickets created - Week of year and don't want to decipher which week of the year it is. 

    Standard calculated attribute 

    Name: Start end of week iso

    DATE_FORMAT(START_OF_WEEK_ISO([Ticket created - Date]),"MMM-dd - ") +
    DATE_FORMAT(END_OF_WEEK_ISO([Ticket created - Date]),"MMM-dd ")

    Computed from: Ticket created - Date

    To get the year and month; i simply stack them in my columns view 

     

    Let me know if you have any questions.

    1
  • Dave Dyson

    Thanks for this tip, McCabe! To make easier for others to search for, would you mind posting this to our User Tips & Tricks topic as well? User Tips & Tricks

    0
  • Andrei
    Community Moderator
    The Wise One - 2021

    Hi @...

    Recently I noticed that the date format in the reports with this Week Started attribute has changed to the long timestamp-like view (without any edits done before). Looks like a bug?  

    1
  • tina y

    Hi, I have been using this for months now and all of a sudden it is no longer working?  Has something changed?

     

    1
  • Justin Federico

    Same issue. We are no longer seeing the date formatted properly in our reports.

    0
  • Zac Renault
    Zendesk Customer Care
    Hi,

    Thanks for your patience.

    A fix has been pushed by our developer team and your issue regarding the DATE_FORMAT function not working as expected as wrongly including the full timestamp should now be resolved.

    Don't hesitate to let us know if this issue is still occurring in your account, but make sure to create different tickets for any other questions you might have in Explore to allow us to better track your requests in the future.

    Have a great day;

    Kind regards.

    Zac
    Technical Support Architect
    support@zendesk.com

    0
  • Patrick Stewart

    Is there a method to only show every 7th day in the selection? My product team wants a filter where they can pick the week of the year to filter down the data. Right now there is a checkbox for every day, but I'd like to only show each Monday.

    0
  • Gab Guinto
    Zendesk Customer Care
    Hi Patrick,
     
    Are you referring to the dates listed as options when filtering by the custom date attribute? If you're using the function START_OF_WEEK_MONDAY in your metric formula, then those dates should correspond to the Mondays of each week. 
     
    You also have other options if you want to display show the end of week dates (END_OF_WEEK_SATURDAY, END_OF_WEEK_MONDAY, etc.). You can refer to the this doc to see other available date functions.
    0
  • Russell Milton

    Ricardo Pinto I've used the formula to create the start of week however which works fantastic.

    However, the sort like time attribute doesn't seem to work for me. I set up the dates as "dd-mmm-yyyy" but it will sort by the day number rather than sorting by the date as a whole.

    eg. it sorts like this
    01/01/2022
    02/02/2022
    08/01/2022
    15/02/2022

    as opposed to this:

    01/01/2022
    08/01/2022
    02/02/2022
    15/02/2022
    0
  • Gab Guinto
    Zendesk Customer Care
    Hi Russ,

    It appears that this is expected when the custom attribute returns an alphanumeric string instead of a numeric date value – the system will just sort the values alphabetically, A workaround is to stick with the format YYYY-MM-dd in your formula, and then just adjust the format via Chart configuration → Date display format. Example, if you want to convert 2023-01-27 to 27-Jan-2023, you can set the custom format to DD-MMM-YYYY.

    This will change the format of the dates displayed in the report, but the dates will still be sorted chronologically as time attribute values. 
    0
  • Kristal Offutt

    I'm trying to figure out how to apply this to changing the week of the year for the time a call started. I keep getting errors and tried playing around but a little lost here. 

    Any help is appreciated - same as the ticket, but instead of week # (1, 2, 3, etc.) for the week of the year of the call having it either with the week start date or the week end date. 

     

    Thank you!

    0
  • Zsa Trias
    Zendesk Customer Care

    Hello Kristal,

    You can replace  [Ticket created - Date] with [Call - Date] in the above formula to apply this recipe to your call data. The formula should be like this: 

    DATE_FORMAT(START_OF_WEEK_MONDAY([Call - Date]),"YYYY-MM-dd")
    0
  • Adarryll Holmes

    Would it be possible to do START_OF_WEEK_THURSDAY? 

    I changed the day of the week to Thursday and got an error message. 

    0
  • Elaine
    Zendesk Customer Care
    Hi Adarryll,
     
    Good day!
     
    To change the start of the week
    1. In Explore, click the admin icon ().
    2. In the menu at the top of the page, click the account information icon ().
    3. Under Start of the week, select the day of the week that should be considered the first day of the week in queries and filters.
    4. Click Update account.

      Here's the article for your reference. Have a great day!
    0

Please sign in to leave a comment.

Powered by Zendesk