Question

Date Parameter Default Setting to yesterday

  • 29 November 2022
  • 2 replies
  • 84 views

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


2 replies

 

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..!

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

 

Reply