Restricting date filter dropdown options (5.0+)

done
reply
normal_priority
(aleks) #1

Sometimes you’ll want to restrict what date dropdown options users can see - either on a dashboard or in the explore itself. Thanks to new looker parameters, you’ll be able to hardcode these options and even give them custom names.

How to do it?

In order to achieve that you’ll need to do the following:

Step 1: Create a date_time type parameter with all desired allowed_values. The label parameter defines how should the value show up in the UI. The value parameter needs an actual looker filter expression. This is where for example you can 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. In 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 end user doesn’t 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.

3 Likes

(Ayon) #2

how can i have this as a dashboard parameter?

0 Likes

(jeffrey.martinez) #3

@ayonsarkar

@will.adams replied to this question on a similar thread here: Restrict dropdown options of dashboard filters

But the short answer is yes! Once you define a parameter, it will become available as an option in the dashboard filter menu. From there you can select what dashboard tiles will listen to that filter.

0 Likes

(James) #4

Would this also work for limiting the list of string search options that are available by default? Or alternatively selecting a default search option? For instance, “contains” is selected by default rather than the first option on the list is selected. This was a super helpful post. Thanks.

keyword-search

0 Likes

(Izzy) #6

There’s currently not a way to set the default filter type, but that’s a neat idea— You should post a new topic in the Feature Requests section for it!

What you can do to get something similar is trick the filters by saying something like default_value: "a%". That’ll have the default value be “Starts with ‘a’”. You can vary your syntax in the default_value field to get it to choose the type you want in some cases— But it’ll also insert a default value, which might not be ideal. Just a thought, especially if you do have a default value you wouldn’t mind applying.

0 Likes

(Vikas Janardhanan) #7

In addition to linking allowed_values to valid looker filter expressions. Is it also possible to link it to enable selecting a specific date using a date picker.
For example to enable the below options in filter for the end user.

  1. yesterday (achievable using allowed_value)
  2. last week (achievable using allowed_value)
  3. is on the day “2019-02-01” which the user selects from the date picker. (is it achievable using allowed_value?)
0 Likes

(Izzy) #8

I couldn’t come up with a way to do this all in one filter using allowed_value. Parameters of type: date begin with free access to the date picker, but adding allowed_values restricts that and only displays the allowed values, like you noticed.

Maybe you could have 2 parameters: One required, one optional. The first would be the one that has allowed_values ‘yesterday’, ‘last week’, or ‘specific date’, and if they picked specific date, then they’d have to add in the optional “date” parameter and pick it there.

That is clunky though… You should post the idea in the Feature Requests section :slightly_smiling_face:

0 Likes