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
- 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)
- 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).
12 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.
Please sign in to leave a comment.