Question
My query displays the week of the year, but I want to see the date that the week starts with instead. How can I do that?
Answer
To change the way attributes display, create a custom attribute.
To accomplish this
- In Query Builder, click the calculations icon (
) on the right sidebar. - Click Standard calculated attribute. You will be redirected to the Formula editor.
- Paste the following custom attribute into Formula editor.
DATE_FORMAT(START_OF_WEEK_MONDAY([Ticket created - Date]),"YYYY-MM-dd")
- Click Save.
- Add the newly created attribute to the query.
You can replace START_OF_WEEK_MONDAY with START_OF_WEEK_SUNDAY or START_OF_WEEK_SATURDAY whichever you want your week to start in.
The calculations for the aggregator MED and AVG are skewed because they are calculated from the values for each day individually. This means seven data points (each for a day in the week), rather than from the values across all tickets throughout the week.
Warning: If your Explore account is not in English, copying and pasting this formula doesn’t work. For a workaround, follow the steps outlined in the article: How can I convert Explore formulas from English into another language?
40 Comments
How do you do this same rename in the Backlog dataset?
Hello fellownarrator!
Could you please try the following formula:
DATE_FORMAT(START_OF_WEEK_MONDAY([Backlog recorded - Date]),"YYYY-MM-dd")
I tried in my test account and it worked. Let me know how it goes for you though!
That worked, thanks Ricardo!
How about for:
Tickets Created - Week of Year
Tickets Solved - Week of Year
Are you trying to do add those attributes in the Backlog dataset fellownarrator?
Those are not available in that dataset. But maybe I didn't understand your question :).
No, what you gave for the Backlog dataset worked just fine. Now, I want to make the same changes to a few reports I have in the Tickets Dataset, specifically for:
Tickets Solved - Week of Year
Tickets Created - Week of Year
Ah! For the attribute "Tickets Created - Week of Year", you can use the example in the article.
DATE_FORMAT(START_OF_WEEK_MONDAY([Ticket created - Date]),"YYYY-MM-dd")
For "Tickets Solved - Week of Year", it should be
DATE_FORMAT(START_OF_WEEK_MONDAY([Ticket solved - Date]),"YYYY-MM-dd")
All you need to do is to change what is inside the [square brackets] with the respective Date attribute.
Got it, thanks again!
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
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:
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.
Please sign in to leave a comment.