Configurable Relative Date Range Option

I know there are several discussion on this forum concerning enhanced date range selection options, but the related ones I've seen mostly involve special cases of the option I have in mind.

 

I would like to see a configurable date range option that behaves as a hybrid of the Last... N Units option and the useful but specific "This Quarter Last Year" option. The goal would be to take one of the existing date range concepts, anchored by the current date, and then providing an offset window that shifts it into the past/future. Essentially, the option would expose these configurable components:

{DateConcept- combo box} {Offset - Integer input} {DateUnit - combo box}

 

DateConcept lets me select the nature of the date range, such as "Previous Month" or "Last N Days" or "Week to Date". Offset lets me put in a number of units to offset, and the DateUnit selector lets me pick Days/Years/Weeks/Quarters/etc to offset by. The number could be positive or negative to control whether we move the window into the past/future (or this could be abstracted into a nice dropdown using natural language e.g. "Ago" vs "From Now")

Thus, if I wanted to compare the week-to-date sales with the same week from last year, I could change the date range for any card from "Week to Date" to "Week to Date" 1 Year Ago.

 

Technically, this feature is achievable right now with a little massaging in beast mode and a carefully chosen time window. I can get Week to Date 1 year ago by choosing "Last 53 weeks by day" and creating a beast mode field like this:

DATEDIFF(CURRENT_DATE(), ADDDATE(`my_date_range_field`, interval 1 year))

Then I filter by this field where its values are greater than or equal to zero. However, this isn't as intuitive as shifting one of the existing date concepts and requires manual beast mode manipulation to set up a date filter that correctly terminates the effective date window.

6
6 votes

· Last Updated

Comments

This discussion has been closed.