Sometimes, you will want to restrict what date dropdown options users can see—either on a dashboard or in the Explore itself. Thanks to Looker parameters, you’ll be able to hard code these options, and even give them custom names.

How to Do It

To achieve this, you need to do the following:

Step 1: Create a date_time type parameter with all desired allowed_values. The label parameter defines how the value should show up in the UI. The value parameter needs an actual Looker filter expression. This is where you can, for example, change Looker’s standard filter expression This Week to show up as Week to date:

parameter: date_filter {
  type: date_time
  allowed_value: {
    label: "Yesterday"
    value: "Yesterday"
  }
  allowed_value: {
    label: "Week to date"
    value: "This week"
  }
  allowed_value: {
    label: "Last week"
    value: "Last Week"
  }
  allowed_value: {
    label: "Month to date"
    value: "This Month"
  }
  allowed_value: {
    label: "Last month"
    value: "Last Month"
  }
  allowed_value: {
    label: "Quarter to date"
    value: "This Quarter"
  }
  allowed_value: {
    label: "Last quarter"
    value: "Last Quarter"
  }
  allowed_value: {
    label: "Year to date"
    value: "This Year"
  }
  allowed_value: {
    label: "Full year LY"
    value: "Last Year"
  }
}

Step 2: Now, you need to add a sql_always_where parameter to the Explore definition. Here, you need to reference the previously created date_filter parameter and underlying date field using Liquid:

explore: order_items {
  sql_always_where: {% condition order_items.date_filter %} order_items.created_at {% endcondition %} ;;
}

If the end user does not select any option from the filter dropdown, the sql_always_where won’t have any effect on the query, resulting in simple 1=1 condition. If a user selects one of the specified options, Looker will automatically translate the selected filter expression into a working SQL WHERE clause.