By writing {{{variable name}}}
in the query of metabase, you can input an arbitrary value and display data using a variable.
This feature can be used to set default values for variables.
However, by default, only a fixed date can be set for a variable's date type, and it is not possible to specify a range such as Last week.
We show how to solve this problem by devising SQL.
Prerequisites.
Metabase uses PostgreSQL as the data source DBMS
SQL representing the default period
Use coalesce and current_date, and use current_date if no variable is set.
SQL extracted from the target
s.created_at
BETWEEN
coalesce([[{{from_date}},]]] current_date - '1 week'::interval)
and
coalesce([[{{to_date}},]]] current_date)
SQL examples for each date using the above
select
sum(user_id) ,
date_trunc('day', created_at) as day
from
a_table
where
created_at BETWEEN coalesce([[{{from_date}},]]] current_date - '1 week'::interval) and coalesce([[{{to_date}},]]] current_date)
group by date_trunc('day', created_at)
order by date_trunc('day', created_at)
Top comments (0)