Use date fitler value at 2 places

Get Help
1

I need to use filter value given in filter at 2 places. Not finding enough resources for doing this. Can someone suggest how to do it?

2

Hi @mohannv, what do you refer to when you say "2 places"?

3

@Luiggi, I have a sub query in my question. The value in date filter is to be used both in outer query as well as sub query.

4

Every time you reuse a filter name in Metabase it will be referenced:
select *
from people
where id = {{filter1}}
and id = (select * from people where id = {{filter1}})

Is this what you're looking for?

5

Hi @mohannv -

I have encountered some nuances here with metabase. As you may have seen, Metabase has different kinds of variables which can be used text, number, date, field filter.

If you're trying to use a field filter, it's going to be tied to a specific column in a specific table. This presents a challenge for dates, because different tables have different columns for dates (per your example an outer query for table1 and a subquery for table2). To get around this, I created a dimension table dim_dates, which is just one row per date (2021-01-01, 2021-01-02, etc. for 50+ years).... so then I join table1 to dim_dates and I join table2 to dim_dates, and then I set my variable as a field filter on the date column in dim_dates. There are many scripts out there for generating a dim_dates table.....

Just to show an example:

    select * 
    from table1 as t1 
    left join dim_dates 
         on t1.date = dim_dates.date
    left join (select * 
                  from table2 as t2 
                  left join dim_dates 
                        on t2.date = dim_dates.date
                 where {{date_variable}}
                ) as sub
      on....some join conditions 
   where {{date_variable}}

Then in the metabase UI, you would set the {{date_variable}} to type of "Field filter" which is mapped to the date column in dim_dates.

So that's how I've solved it.... That said, I wonder what a variable with type = Date does.... maybe that's another way to do this. But variable type = field filters are really nice because you can use Metabase's built in "Date filter" widget type which has a done of date filter options.

Hope that helps! -J.