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. -
I am trying to create a report that can be run on Mondays to show all tickets created the previous Saturday and Sunday. Is there a way to create a custom date filter that I can just select from the list to provide the this or is it possible to set a custom filter of (Today - 2 days) to (Today - 1 day) rather than having to manually select the date range?
-
Hi Erik Cerbulis, you can do this by adding the Ticket created - Date attribute as a filter and then taking the following steps:
- Click the filter you just added.
- Click Edit date ranges, and then go to the Advanced tab.
- Under From the beginning of, select 2 days in the past.
- Under To the end of, select Yesterday.
- Click Apply.
For more help with filters, see Working with report filters.
Each Monday that you run your report, that filter will show you the previous weekend's tickets.
-
Awesome! Thank you so much for the quick assist.
-
Eugene Orman Is there any update on the full flexibility of using Advanced date ranges with custom date attributes? Not just the start of week?
-
The option to “edit date ranges” is currently only available for default date attributes but not custom date attributes. This doesn’t seem entirely logical since both the default and custom time-based attributes ultimately perform the same underlying function so both should have the ability to edit date ranges rather than only having it available for the defaults.
I’ve found that adding filters as a work-around often changes the data displayed by the report. For example, if I want just the past four weeks of results to be displayed for a custom Start of the Week calculated attribute and add a “Ticket created – Date” filter that I then create a custom date range for, most, if not all, of the numbers change for those dates change which negatively impacts the accuracy of the report. Depending on the report, some numbers will only change minutely whilst others have a massive change but none of the changes are accurate since they shouldn't change at all. But an "edit date ranges" feature for a regular time-based attribute doesn't result in this type of change.For example, adding a "Ticket created - Date" filter to this report alters some of the numbers in the datatips. Shown here, the numbers in the red rectangles are different once adding the filter simply to show the past 3 months of results. This further changes when the report is added to a dashboard, after which all the numbers in the report change. But, prior to adding the filter, adding the report to the dashboards changes nothing and the numbers still don't change when time filters in the dashboard are used.
Changes in the Report Builder:
Changes when placed in a dashboard:
サインインしてコメントを残してください。
10 コメント