Date Parameter Default Setting to yesterday

[Sebastian_G]

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

[NoamT]

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

[sneal88]

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]

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

[joanne_boyd-165]

Has anyone found a solution to this? 

[levon_gocci]

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. 

[jcdufault]

I think you'll need to do some more hacking. How are you injecting your parameter? I think you can't hack a default date based on your database's results into the parameter default value.

Here's what I think you need
In your view declaration:

view: my_kpi_table {
  #Replace This:
  sql_table_name: "SCHEMA"."KPI_TABLE" ;;
  #With this:
  derived_table: {
    sql: SELECT *
         FROM SCHEMA.KPI_TABLE
         WHERE 1=1
           {% if is_latest_date._parameter_value == 'y' %}
           #change the SQL logic to whatever you need
           AND KPI_TABLE.KPI_DATE = dateadd(day, -1, (select max(created_at_local_date) from schema.kpi_table))
           {% else %}
           AND KPI_TABLE.KPI_DATE = {% parameter dateselector %}
           {% endif %}
         ;;
  }

  #All your dimensions and measures below
  parameter: dateselector {
    label:"Date Selector"
    type: datetime
  }
  parameter: is_latest_date {
    label: "KPI As of Latest Date"
    description: "Set this filter to Yes to default to the latest available date. Set this to No and the date in the Date Selector Filter will be used. 
    type: unquoted
    allowed_value: {
      label: "Yes"
      value: "y"
    }
    allowed_value: {
      label: "No"
      value: "n"
    }
    default_value: "y"
}

What happens here is if you put the `is_latest_date` parameter to yes, it'll get the latest date from your table. If you set it to No, it'll default to get whatever date has been chosen in the date selector.

Let me know if that solves your issue

[salehaxsid]

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

this piece of code will work with filter and not parameter

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