This recipe will show 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
- In your query, click the Calculation engine menu icon () on the right toolbar, then select Standard calculated attribute.
- Name your attribute ‘Weeks’ or ‘Week Bucket’. You can adjust it later if needed.
- 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 will need to update
[DATE(Date)]to the name of the date attribute you would like to use. For example this could be Ticket Solved At, Assignee Created At, etc.
- Click Save to create your 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 simply edit the formula and remove
+ " " + "to" + " " + DATE_FORMAT(END_OF_WEEK_MONDAY([DATE(Date)]), "YYYY-MM-dd").
The complete formula would look like below: