Multiple date ranges from one column

[plg6]

Good day,

I have a table of sales by days, and I want to be able to allow users to select days between two ranges, for example, start of the last fiscal year to the date a year ago, then a second range of the start of the current fiscal year through today. Using date ranges either embedded into question filters or as dashboard filters seems to have them work with a Boolean AND result, whereas what I need is Boolean OR. This code in native SQL works fine:

select fisyr,sum(sales_dollars),sum(budget_dollars) from daily_sales_summary
where (post_date between "2021-07-01" and "2022-03-06") or
(post_date between "2022-07-01" and "2023-03-06")
group by fisyr
order by fisyr
;

How do I translate this into Metabase for multiple filters? I've seen where I can set one up but am not yet sure how to do more than one at the same time.

Thanks!

[nllho]

Hi! You can use field filters in your SQL query.

Your SQL query will become:

select 
fisyr,
sum(sales_dollars),
sum(budget_dollars) 
from daily_sales_summary
[[where {{post_date_range_1}} or {{post_date_range_2}}]]
group by fisyr
order by fisyr

Then you will need to make post_date_range_1 and post_date_range_2 field filters with the widget type "Date Range".

It should look something like this (using the Sample Database):

Let me know if this helps!

[plg6]

Thank you! Works great!