In this Explore recipe, you'll learn how to create a Standard calculated attribute that reports on data by week with Sunday as the start of the week. The built-in Week of year attributes will show weeks based on a start date of Monday with the end date of Sunday.
What you'll need
Skill level: Moderate
Time Required: 10 minutes
- Zendesk Explore Professional or Enterprise
- Editor or Admin permissions (see Giving agents access to Explore)
- Ticket data in Zendesk Support
Creating the attribute
- In Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > Tickets > Support: Tickets, then click New query. Query builder opens.
- From the Calculations (
) menu, click Standard calculated attribute.
- On the Standard calculated attribute page, enter or copy and paste the formula below into the attribute editor.
LEFTPART(START_OF_WEEK_SUNDAY([Ticket created - Date]),10)
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - Give the attribute a name like Ticket created - Sunday-Saturday.
- When you are finished, click Save.
Keep in mind that this attribute is reporting on the week the ticket was created. If you need to report on the week the ticket was solved, edit the attribute above and replace [Ticket created - Date] with [Ticket solved - Date].
You can now use this attribute in your queries. The display format of this attribute will show the start date of the week (i.e. the date of the Sunday that represents the start of the week).
18 Comments
Hi, I tried to do this for week starting on Saturday, but there's no option to edit the date range:
Are there additional steps I'm missing?
Thank you!
Hey Kirsten,
I'm going to create a ticket on your behalf so our Customer Advocacy team can dig into this further for you. You'll receive an email shortly stating your ticket has been created.
Cheers!
I am experiencing the same issue as Kristen:
Use case: I need to automate reports to be sent out weekly, which scheduling is the easy part but I am having difficulty creating a Sunday-Saturday ticket solved - last week attribute to use. Is there something missing from the formula?
Is there anyway to make this update to the previous week to effectively automate a distribution schedule?
Hello William Chase Myer,
I've gone ahead and opened a ticket on your behalf so our Advocacy team can look into this deeper for you. You'll receive an email shortly stating your ticket has been created.
Best regards.
Hi, I am having the same problem. Was the issue resolved? If not could you open a ticket on my behalf please.
Hi Ryad-Malik,
Custom date/time attributes will not have the “Edit date ranges” button. Dynamic ranges are only available for default date/time attributes.
Hi,
I used the formula and it returns the week start date however it seems to be splitting the results between two lines when compared to the week of year attribute. Is there a way to get a better understanding of how this function works? I would assume that the week start date and the week of year when used together should return results that do not split between rows. Ticket solved was used for both attributes (week of year and week start date formula).
Hello smithy smith,
This is because the built-in Week of year attributes will show weeks based on a start date of Monday with the end date of Sunday. The above recipe creates a Standard calculated attribute that reports on data by week with Sunday as the start of the week. These attributes are not meant to be used together because they are define weeks differently.
If you want an attribute that shows the date the week started for the week of year attribute, you'll need to create this attribute instead.
I just have to say, this shouldn't be so convoluted. I've not see a calendar that doesn't start on Sunday. Should just be an option in Explore that the week starts on Sunday or Monday.
Thanks for that feedback, Larry.
I am experiencing the same issue as Kristen and William. Is there a plan to have dynamic data ranges? Or at least have the option of picking last week data for every week's schedule report delivery?
Hello Pavel Grozev,
I've opened up a ticket on your behalf so our team of Advocates can look into this for you. You should receive an email shortly followed by a reply from one of our experts.
Best regards.
Hi,
I used the time filter (in the query) to display only last 8 weeks. But when I have a query on the dashboard and apply the 'last week' time filter it looks like the results are messed up.
Our team provides support from Monday to Friday. As the tickets created on weekends should be included in the next week report, I assume that my week should start on Saturday. Is that correct?
The query looks like this;
Can you explain how this calculated attribute will work with 'last week' or 'this week' time filter?
Thank you!
Hello Katarzyna KRZAK,
So the best way to accomplish this would be to utilize time filters in your dashboard. I've linked an article below that goes into detail on how to implement this in your Explore instance.
Best practices for using dashboard filters
Best regards.
We're experiencing the same limitation as Kristin, William and Pavel. We'd like to be able to see the rolling 3 week date range without having to manually pick and choose weeks each time we want to review the latest data for our reports.
Similarly, it's not user friendly to use the default metric of Week 1, 2, 3 etc. Is there any plan to allow advanced date ranges?
Hello John Tieu,
With this issue, unfortunately, that is expected behavior. Custom metrics and attributes do not have the functionality to do the "edit date range" function at this time. A workaround would be to go in and manually select the dates you want, or use the metric as a filter and select it up in the filter. Either way, it does require manual selection.
Best regards.
To those trying to filter dynamically, I did something that always gives me the current week starting Sunday and ending Saturday. Basically you're creating a true or false flag and then just filter on that. My flag will allow to see the full week in review as well as I've based it on the prior day's date.
LEFTPART(START_OF_WEEK_SUNDAY([Chat started - Date]),10)=LEFTPART( START_OF_WEEK_SUNDAY(DATE_ADD(TODAY(),"day",-1)),10 )
Thanks for taking the time to share this Matt!
Please sign in to leave a comment.