Filter with Pre-set Date Range

Hey QS team, happy Monday.

I would like to create a control that toggles a multi-series line chart’s x-axis between different date ranges (i.e. 7d, 1m, etc.) In the screenshot below you can see I’ve created the control but it isn’t functional yet. I created a calculated field that assigns each row with the options you see in the toggle photo. Now that I have a calc field with 7d, 1m, 3m assigned to each row, what’s next? I am stuck at this point.

Bonus question: Is there an AND function equivalent for calculated fields? For example, I want ‘7d’ to = date_column >= now()-7 AND date_column < now()
Thank you!

you would need to group your x-axis down to the range. Check out this for more information.


Is showing these three custom date ranges important or are you just looking for relative date range filtering?
I will explain both options starting with relative date range filters.

Relative date filtering
With the visual selected, add a filter on the date field.
Change the Filter type to Relative dates, Period to Days, Range to Last N days and put in a starting default for the Number of days as (say) 30.
Click Apply and the visual will filter down to the above setting.

Click the ellipsis against the filter name and then Add to sheet.
You will now have a relative date control on your sheet and this will let your users control the relative filtering as desired (even changing the period from days to month, year etc)

Note - The relative date range is calculated from UTC. If you need it calculated based on a custom time zone, you can pass in that date and time value as a parameter.

Custom date filtering
On other hand, if your intent is just to give users the three preset options calculated against now() as you mentioned in your second question, you can do the following.

Create a single value string parameter and create a control on top of that with the three static values (7d, 1m, 3m). I assume that is what you already did to capture the screen shot.

Next, create a calculated field to drive the date inclusion/exclusion. (See below)

Date Filter

ifelse ( 
        addDateTime(-7,'DD',now()) <= {Order Date} and {Order Date} <=now() and ${pDateRange} = '7d', 'Include',
        addDateTime(-1,'MM',now()) <= {Order Date} and {Order Date} <=now() and ${pDateRange} = '1m', 'Include',
        addDateTime(-3,'MM',now()) <= {Order Date} and {Order Date} <=now() and ${pDateRange} = '3m', 'Include',

Then, create a filter for the visual using the above Date Filter calculated field and set it to pull in only ‘Include’ value.

Marking this as solution to your question. Please let us know if you have more questions.

Arun Santhosh

1 Like

Thank you, @ArunSanthosh! This is the solution I was looking for.

Hi Jeff @jl-thirdwave ,

Glad to know. Most welcome :slight_smile:

Arun Santhosh