Date Parameter Default Setting to yesterday

Hi there,

I’m quite new to Looker (a few weeks now) and got stuck at a Parameter default topic.

The parameter is a date and I’m using the parameter to calculated KPI on that date and relative to that date.

As the parameters default value has to be a string on the one hand side and a literal date or datetime” on the other side, I somehow dont get it done to get my parameter to yesterday as default.

I tried all kinds of stuff… like this:

  parameter: dateselector {
type: date
description: "Use this field to select a date to filter results by."
default_value: "${date_selector_default}"
}

dimension: date_selector_default {
type: date
hidden: yes
sql: DATE_ADD(MAX(DATE(${created_at_local_date})), INTERVAL -1 DAY) ;;
}

or just

parameter: dateselector {
type: date
description: "Use this field to select a date to filter results by."
default_value: "Yesterday"
}

But nothing worked and hours of google search also didnt really solve it.

Thank you for reading until the end 😉

It would be great if someone could help me out here, thank you.

Best, Sebastian

0 5 2,660
5 REPLIES 5

i was also breaking my head on this for some time now. there is currently no way of having parameter set to a dynamic date (like today, yesterday, etc) by default.

I found a solution that is working well for me: removing the default value in the parameter, so when empty it will be “null”.

Then, injecting the dynamic date into the dimension’s SQL using COALESCE({% parameter %},CURRENT_DATE-1) in the sql itself.

to your example:
 

 parameter: dateselector {
type: date
description: "Use this field to select a date to filter results by."

}

dimension: date_selector_default {
type: date
hidden: yes
sql: DATE_ADD(MAX(DATE(COALESCE({% parameter dateselector %},CURRENT_DATE))), INTERVAL -1 DAY) ;;
}

Hope it helps..!

Hi @NoamT  - it doesn't look like I am able to set default value to null for date parameter (even if i leave blank). It just seems to automatically default to current date. have you gotten it to behave differently on your end?

jcdufault
Participant II

Not sure if this is helpful in your use case but you can always use the `Matches (Advanced):` option and input “yesterday” and it will always default to yesterday’s date when loading the Look/Dashboard

67891058-f5f0-4a1c-9415-2c260834fc60.png

Has anyone found a solution to this? 

I havent had any success doing this and I desperately need it. Anyone with a working solution. @NoamT  I could not get your solution to work. 

Top Labels in this Space
Top Solution Authors