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

Have more questions? Submit a request

18 Comments

  • Hillary Latham
    Comment actions Permalink

    I think your instructions meant to say click on "Standard calculated Attribute", not "standard calculated Metric", yes? However, it's not working for me.  When I add it to a query, to replace "Ticket created - Week of year", I get an error.

    0
  • Ricardo
    Comment actions Permalink

    Hello, Hillary,

    Thanks for the heads-up. I have corrected the article. It is indeed Standard calculated attribute.

    The formula was also updated to:

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

    It is "Ticket created", not "Ticket Created". 

    Let me know if this helps!

     

    0
  • Scott
    Comment actions Permalink

    Hi Ricardo,



    Thank you for your reply. It doesn't look like the edit was saved correctly, as the article is still showing the errors mentioned.



    When I try to apply this custom attribute to sort my results, the results themselves are affected, specifically % Satisfaction Score. This metric appears normally when sorted by Ticket created - Week of year, but if I replace it with Ticket created - Start of week the values are 6-7x their normal amount.

    Why is this happening? I have toggled the Sort like time attribute box and tried various Computed from fields, none of which have changed my results.



    Additionally, is it possible to select a date range for custom time attributes? It seems that I will always need a date filter along with a custom time attribute like this if I want to select a range/repeat pattern.



    Thanks in advance for your help.


    Attachments:

    0
  • Ricardo
    Comment actions Permalink

    Hello, Scott,

    Thanks a lot for the heads up. I have re-edited the article to display the right formula and the right text.

    Regarding your issue, I was able to reproduce it in my test account. I will need to create a ticket to investigate further. You should have received an email in your inbox.

    0
  • Samuel Alberto
    Comment actions Permalink

    Hey Scott, looks like you're using SUM as your aggregator so what that is doing is aggregating the satisfaction scores from all seven days of the week and representing that sum on one day of the week. Perhaps you would want to use AVG instead of SUM. The week of year table configuration probably takes this into account because it is a default attribute so this may be why you should use a different aggregator in your custom calculated attribute.

     

    0
  • Scott
    Comment actions Permalink

    Hi Samuel,

    Thank you for your suggestion. However, SUM is the only available aggregator for the % Satisfaction Score metric.

    0
  • Job Thomas
    Comment actions Permalink

    If I try to create a different format - such at "dd MMM YY" it is displaying correctly but it is no longer sorting it according to the actual date, but first according to day (so the weeks starting on 1 July will be before the one starting on 3 May), and the same goes with other orders. How can I have a nicer looking format, but still have a logical sorting?

    0
  • Job Thomas
    Comment actions Permalink

    Added: I'm facing the same problem as Scott is and can indeed see that the limitation of the %Satisfaction Score being the SUM rather than the AVG is causing this.

    1
  • Scott
    Comment actions Permalink

    Solution from Tier 2 Support to implement the AVG aggregator:

    While SUM (% Satisfaction Score) will work harmoniously with Ticket created - Week of year (where weeks are displayed in week numbers), it will be skewed when Ticket created - Start of week (where weeks are displayed in start dates) is used, as observed in your screenshot. 

    This is caused by the SUM aggregator as it does not work well with custom formulas like DATE_FORMAT(START_OF_WEEK_ISO([Ticket created - Date]),"YYYY-MM-dd "). And since SUM(% Satisfaction Score) is a default metric (and thus cannot be edited). We will need to create a new metric(essentially identical to % Satisfaction Score) and use the AVG aggregator instead:

    Note: we will also need to update the Display Format for the new metric to "%" for the percentages to appear properly

    1
  • Ricardo
    Comment actions Permalink

    Thanks for sharing, Scott!

    0
  • Chen Shimon
    Comment actions Permalink

    Hey,

     

    I followed the instructions here and once I changed the chart type to bar graph the date format changed: 

     

    Any idea how I can fix it?

    Thanks!

    0
  • Austin Lacey
    Comment actions Permalink

    Hi Chen!

    If you navigate to Chat Configuration -> Date Display Format you should be able to change the format of how the dates display on the axis. 

    0
  • Chen Shimon
    Comment actions Permalink

    Thank you!

    0
  • Michele Italo D'Amato
    Comment actions Permalink

    the problem is that, using this custom attribute, I lose the capability to drillthrough a subset of data. It's pulling everything instead of just the clicked column.

    1
  • Dan Kondzela
    Comment actions Permalink

    Hey Michele, thanks for reaching out. This is valid feedback, but for the time being this is behaving as expected. I wish I had more productive feedback for you for the time being. 

    I would recommend making your voice heard on our Explore Product Feedback forums.

    0
  • Jamison
    Comment actions Permalink

    This is a great tip, thank you!

    You can also add the end of the week to create a date range:

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

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

     

    I modified your formula a bit so it will show Aug 26 - Sep 01

     

    0
  • Lauren
    Comment actions Permalink

    Hi,

     

    I'm having trouble with this custom attribute - I've followed the instructions fully but the dates are all muddled up:

     

    If I try to filter them it just shows me a bunch of random dates. How can I make it show the last 3 or 4 weeks?

    I tried to look for the Chart Configuration > Date Display Format but it appears I don't have it.

     

    Thanks!

    0
  • Brett - Community Manager
    Comment actions Permalink

    Hey Lauren,

    Any chance you could provide a screenshot of the attribute you created so we can take a look?

    Thanks!

    0

Please sign in to leave a comment.

Powered by Zendesk