Set default date filter

Get Help
1

I want to set default date filter when user doesn't not select any date filter from dashboard.

I tried like below but it did not work(I worked with string format date value field, named "date_ymd").

where to_date(date_ymd, 'yyyymmdd') between [[{{start_date}}]] date_format((current_date - interval '1' month), '%Y%m%d') and [[{{end_date}}]] date_format((current_date - interval '7' day), '%Y%m%d')

Please give me any help to set default value for start_date and end_date filters.

2

Hi @daniel.lee
You would use Optional Clauses or Complex Default Values:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html
So either optional:

coalesce([[ {{filter}} , ]] current_date)

Or complex defaults (using the comment syntax matching whichever database you are querying):

[[ {{filter}} --]] current_date
3

thanks @flamber,

can I use optional clause or complex default value with date between clause?

if you can, any example code will be much appreciated.

4

@daniel.lee Please read the documentation pages, since they provide a good understanding:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html
https://www.metabase.com/learn/building-analytics/sql-templates/sql-variables.html
https://www.metabase.com/learn/building-analytics/sql-templates/field-filters.html

where to_date(date_ymd, 'yyyymmdd') between coalesce([[{{start_date}},]] date_format((current_date - interval '1' month), '%Y%m%d')) and coalesce([[{{end_date}},]] date_format((current_date - interval '7' day), '%Y%m%d'))
5

Thank your your kind help, I read the docs and know it give a great idea.
however, I've been having trouble applying into my workaround.

where to_date(date_ymd, 'yyyymmdd') between coalesce([[{{start_date}},]] date_format((current_date - interval '1' month), '%Y%m%d')) and coalesce([[{{end_date}},]] date_format((current_date - interval '7' day), '%Y%m%d'))

I tried your example, and it gives an error like this. I don't get used to use square brackets so I cannot understand why it says "coalesce" has not enough arguments.
image

6

Solved like below.

and to_date(date_ymd, 'yyyymmdd') between [[{{start_date}} -- ]](current_date - interval '1' month) and [[{{end_date}} -- ]](current_date - interval '7' day)

@flamber
Thank you for your kind help :slight_smile:
Your comment helped me pretty much.

7

@daniel.lee Since you don't provide information about which database you are using, then it's difficult to know which quirks it has.
Then use coalesce([[ {{filter}} , ]] current_date, null)

Your "Solved like below" will not function.
It needs to be with new lines, since anything after the comment syntax is not read otherwise:

and to_date(date_ymd, 'yyyymmdd') between
   [[{{start_date}} -- ]](current_date - interval '1' month)
   and [[{{end_date}} -- ]](current_date - interval '7' day)