Default value in field filter {{date}} variable to now()

Hello,

I read that I had to use something like [[{{date}} #]] current_date() to have some defaults loaded with this kind of filter (year and month) :

I want to have the month and year set to current month and year if nothing is selected, but it’s not working.

Value of {{date}} when no default is selected is set to “1=1”.

Any idea how to achieve this kind of trick ?

Thanks.

Hi @prigal
Not sure when “complex default value” stopped working, but there’s an issue in 0.31.2
https://github.com/metabase/metabase/issues/8640
And then there’s a feature request for doing this in the UI instead of SQL - go and upvote by clicking :+1:
https://github.com/metabase/metabase/issues/3156 - and look at the last comment for a workaround

Thanks for your info. I have added my +1

Not sure it that helps, but here is what I am doing to have a date equal to today if the variable is empty:
define the variable “datepick” with a default value, old enough to be not existing in the database (let’s say the DB was empty in 2016, so we define the default value as something in 2016).

Then, let’s say I have a field named creation-date
WHERE date(creation-date) = if(year({{datepick}})=2016,current_date,{{datepick}})

If there is nothing in the date variable field, then the current date will be used, otherwise, the value in the field will be used.

This is more flexible than [[{{date}} #]] current_date() notation, as you can easily use it in more complex situation.
Let’s say I want to select all dates in the last 7 days:
WHERE datediff(if(year({{datepick}})=2016,current_date,{{datepick}}),date(creation-date)) between 0 and 6

1 Like