This recipe shows you how to display your weekly data in date buckets rather than just showing the individual week number.
What you'll need
Skill level: Medium
Time required: 20 mins
- Zendesk Explore Professional
- Editor or Admin permissions (see Adding users to Explore)
- Ticket data in Zendesk Support
Create your calculated attribute
To start, you will need to create a standard calculated attribute (see Creating standard calculated attributes).
To create your date buckets calculated attribute
- In Zendesk Explore, click the query (
) icon. - In the Queries library, click New query.
- On the Choose a dataset page, click Support > Ticket updates > Support: Tickets, then click New query. Query builder opens.
- In your query, open the Calculations menu (
), then click Standard calculated attribute. - On the Standard calculated attribute page, under Name, enter a name for the attribute, like Week Bucket. You can change this name later if you want.
- In the Formula field, enter or paste the formula below:
DATE_FORMAT(START_OF_WEEK_MONDAY([DATE(Date)]),
"YYYY-MM-dd") + " " + "to" + " " +
DATE_FORMAT(END_OF_WEEK_MONDAY([DATE(Date)]), "YYYY-MM-dd")Note: You need to update [DATE(Date)] to the name of the date attribute you would like to use like Ticket Solved - Date, Ticket First Assigned - Date, etc.
For example, using Ticket Solved - Date, replace [DATE(Date)] with [Ticket Solved - Date]. - Click Save to create the calculated attribute. .
Display your results
After you created your calculated attribute, you can add it and any other data to your query. When using the calculated attribute, rather than a pre-built week attribute, your results will display like the image below:
Alternatively, if you’re only interested in seeing the start of the week rather than the full duration, you can edit the formula and remove + " " + "to" + " " + DATE_FORMAT(END_OF_WEEK_MONDAY([DATE(Date)]), "YYYY-MM-dd").
The complete formula would look like below:
DATE_FORMAT(START_OF_WEEK_MONDAY([DATE(Date)]), "YYYY-MM-dd")
3 Comments
Hi Andrew, This article was helpful. I have a need for all Explore Weekly Reporting to start Sunday and end on Saturday. The current default Week in Explore starts on Monday and ends on Sunday.
Will I need to build out some weekly metrics to have a Sun-Sat work week?
Insights allowed a couple choices of week start and end dates.
Can the be a feature Request for Explore?
Thanks, ~Terry (Evernote)
Hey Terry,
You can build out some custom metrics to track weeks that start on Sunday. The formula looks something like: START_OF_WEEK_SUNDAY(_date).
We don't currently have it on the roadmap for this to be a native feature, but it is possible with a Calculated Attribute. I'll bring it up with our Product team to see their thoughts!
I think the syntax of the example provided is incorrect because I keep getting an error when copying it and attempting to put in a field name. Specifically, the brackets are in the wrong place.
The field name should be enclosed in brackets, not the DATE function. For example:
At least, that's what I had to do to get this to work.
Please sign in to leave a comment.