Ability to set a custom date range for custom date attributes
As I build custom standard calculated attributes for date attributes, I would like the ability to set a custom date range for those custom date attributes, so that I can easily filter date ranges on my custom attributes in the same way that I can for the default (built-in) date attributes.
For example, reports for my organization call for an adjusted start of the week on Fridays other than the default Monday. This requires a standard calculated attribute to be created to create a new attribute where the week starts on a different day. However, my custom date attribute cannot be filtered with a range like the built-in week attribute can.
-
正式なコメント
We’re excited to announce that you can now set a custom start of the week in Explore! You can use this instead of the calculated attributes.For details, see Setting the start of the week for reports and filters.
-
This would be extremely useful and not sure why this is not a feature already!
If not being built soon it would be great to see some recipes on how to create formulas to view items in the current week, day, etc.Thanks!
-
Hi @..., as a workaround, you should be able to create a query using your standard calculated attribute (as you mentioned above), and then add a built-in date attribute as a filter with a custom range. So here’s what I did to create a report that shows me how many tickets were created each week, where the week starts on Friday:
- I created a standard calculated attribute with the following formula (adapted from this recipe):
DATE_FORMAT(START_OF_WEEK_FRIDAY([Ticket created - Date]),
“YYYY-MM-dd”) + ” ” + “to” + ” ” +
DATE_FORMAT(END_OF_WEEK_FRIDAY([Ticket created - Date]), “YYYY-MM-dd”) - In the Metrics panel, I added COUNT(Tickets), and in the Rows panel, I added the attribute I just created above.
- Next I added a filter of Ticket created - Date.
- To create a custom range for the filter, I clicked the attribute filter and went to Edit date ranges > Advanced.
- I set the From the beginning of setting to 8/27/2021 (a Friday) and the To the end of setting to 9/2/2021 (a Thursday).
- Then I set the Repeat for setting to 4 weeks in the past. (You can set this number to whatever time period you want to report on.)
The resulting query now shows me how many tickets were created in each of the last 4 weeks, starting on Fridays. This process isn’t as quick as picking one of the simplified ranges for built-in attributes, but the end result is the same. Just make sure your custom attribute is using the same event as your filter attribute (in my example, both are looking at Ticket created - Date).
Hope this helps!
- I created a standard calculated attribute with the following formula (adapted from this recipe):
-
Hi @..., this short article is a great example of how to view items for the current month (or day, week, etc.): How do I make my metric show always results for the current month?
Some other good reads on working with dates in Explore are:
-
+1! Having date ranges filter on custom date attributes would be a great help!
Current set up doesn't allow applying dynamic filters on such fields and the workaround (adding additional custom attribute built on the custom date attribute) is not working well in Explore- it takes a long time to load and at the end query gives no results or there is a message stating that results couldn't be loaded because the query took too long to run.
サインインしてコメントを残してください。
5 コメント