loading...

Make the default of a variable of type Metabase date relative

konyu profile image @kon_yu ・1 min read

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)

ref: https://www.wantedly.com/projects/432393?fbclid=IwAR1_jZ0-xxi2jSl3CIlA-j9PDLZOz-msEGEWc5ekeMxPQB38fIbw5fsyBD4

Discussion

pic
Editor guide