This recipe shows you how to display your weekly data in date buckets rather than just showing the individual week number.
Skill level: Medium
Time required: 20 minutes
- 1 calculated attribute
- Data to measure
Create your calculated attribute
To start, you will need to create a standard calculated attribute (see Creating basic calculated attributes).
To create your date buckets calculated attribute
- Create a new query using the Support: Tickets, Users, Organizations dataset.
- In your query, open the Calculations menu (), then click Standard calculated attribute.
- Under Name, enter a name for the attribute, like Week Bucket. If you want to, you can change this name later.
- Enter 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 updateFor example, using Ticket Solved - Date, replace [DATE(Date)] with [Ticket Solved - Date].
[DATE(Date)]to the name of the date attribute you would like to use like Ticket Solved - Date, Ticket First Assigned - Date, etc.
- 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: