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 or Enterprise
- Editor or Admin permissions (see Giving agents access 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].
Tip: If you're working in a language other than English, read this article to help you enter Explore formulas in your language. - 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")
19 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.
I keep getting an error that is difficult to track down "An error has occurred while evaluating Week Bucket for the following values: Ticket solved - Date -> ."
I have the following as my calculated query:
Is there something wrong with the syntax of the query I have, or is the error related to something else?
Hi Nicole, I tried this query and got the same error as you did. I'm going to create a ticket for this so we can investigate it properly and get this working for you. Someone will be in touch soon.
Feels like something that should come out of the box, rather than having to create a custom calculated attribute.
Hello Allen Lai,
I appreciate the insight on Explore. I would suggest posting your thoughts in our product feedback forums so our developers can consider it for a future update in the future.
Best regards.
Hey Devan - Community Manager is there a Zoom or Hangouts for the AMA tomorrow? How do I sign up?
I had this working fine for the last year or so, recently it has stopped working. I am using this syntax:
DATE_FORMAT(START_OF_WEEK_MONDAY([Ticket created - Date]), "YYYY-MM-dd")
If I apply the generic "Ticket created - week of year" attribute to my query (instead of my custom Weekly Bucket attribute), it calculates and displays the results just fine, however the weekly labels are "1,2,3,4,...etc". All I want to do is translate "1,2,3, etc" to "2020-01-01, etc".
Seems like there should be a way to do this without creating a custom attribute, but given that I cannot seem to do this can someone check my syntax?
This query has recently stopped working for me as well Ellen, I have a support case open, but have not heard anything back. Hopefully, it is something that can be fixed easily, or something can replace it, I need these reports for management.
Hi Ellen and Nicole. I've opened a ticket about this so we can investigate what's going on here. Based on the findings, I'll make any necessary doc updates. Thanks!
This recipe worked well for me, but I'd be interested to see the weeks as e.g CW01, CW02,...
Does anyone know how to change the code to the calendarweeks instead of the dates?
I have the exact problem except that the week starts on a Sunday.
Is it possible to adjust reporting for your start day (Monday - Sunday)?
As you can see, the start date for viewing this week is based on the 14th - the 20th.
Secondly, is there also a way to use regular date formating, like 31/12/2000?
Hi Bart, there are a bunch of different START_OF_WEEK functions as detailed in https://support.zendesk.com/hc/en-us/articles/360022184834 including START_OF_WEEK_SUNDAY. Might this help you?
What would be the formula for Created vs Resolved query?
I need to display the data weekly.
The following formula is not working, because the ticket numbers displayed are incorrect (do not add up)
DATE_FORMAT(START_OF_WEEK_MONDAY([Ticket updated - Date]),
"YYYY-MM-dd") + " " + "to" + " " +
DATE_FORMAT(END_OF_WEEK_MONDAY([Ticket updated - Date]), "YYYY-MM-dd")
Hello Jiri,
To accomplish this, you will need to make two reports and make some changes in your formula. \
Once you implement these changes, you should be able to run your reports will no issue.
Best regards.
Hi Devan.
I was able to find the solution on the weekly, but we switched to EazyBI. This is feature is out of the box there and EazyBI is much better reporting tool in comparison to Explore.
Thanks.
Hi this is great thank you for sharing, I am trying to replicate the same scenario except with ticket updates dataset for public comments made each week, however, I get errors using D count public comments and when using just comment public I don't get WoW results
DATE_FORMAT(START_OF_WEEK_MONDAY([Comment public]),
"YYYY-MM-dd") + " " + "to" + " " +
DATE_FORMAT(END_OF_WEEK_MONDAY([Comment public]), "YYYY-MM-dd")
and tried
DATE_FORMAT(START_OF_WEEK_MONDAY([D_COUNT(Public comments)]),
"YYYY-MM-dd") + " " + "to" + " " +
DATE_FORMAT(END_OF_WEEK_MONDAY([D_COUNT(Public comments)]), "YYYY-MM-dd")
Any suggestions? Thank you
Hi Jesse Marion,
The issue is that you are trying to put in a metric instead of a date attribute. Try this instead:
I hope this will give you the result, that you are looking for.
#helpsome regards,
Marie-Cathrine Sørensen
Business Intelligence Specialist & Junior Developer @ helphouse.io
Please sign in to leave a comment.