How to Remove "Any Time" Option from Date Range Filter

[aleishon]

Hi everyone!

I'm using a Date Range filter in my looker dashboard, and I want to remove the "Any Time" option from the filter dropdown (as shown in the attached screenshot). I would like to limit the available options to specific date ranges like "Is in the month", "is in the year", etc., to prevent users from selecting "Any Time".

Is there any way to configure the Date Range filter to achieve this?

[sam8]

It's possible, but not easy! You'd need to create a parameter with the specific values you want to show, then reference that parameter in a sql_always_where statement using Liquid. 

It's a bit involved, but I can show you an example if you're truly interested. This is a really good feature request!

[aleishon]

---

---

Thank you very much, Sam!

I’m really interested, as I don’t want my users to have too many options; they are still new to using Looker. I’d appreciate it if you could share an example.

Thanks a lot, and I look forward to the example!🤗

[sam8]

Okay, one thing I realized that might help you - before we dive into the depths of LookML. 

It looks like your dashboard filter is set to "Advanced". If you change it to "Timeframes", you might find that the presets are closer to what you want!

 

Does that work for you?

[aleishon]

I already looked into that option, but I don’t have daily data, I only have monthly data for the last 24 closed months. That’s why the timeframes options didn’t work for me.

[sam8]

All right, then. Let's get into the LookML.

At a high level, we're going to create the following:

1. A parameter to restrict the filter options to the end user.
2. A few dimensions that define the filter SQL.
3. A sql_always_where that applies the filter SQL based on the chosen parameter value.

Step 1: Create the parameter

Create a parameter field in the same view as the date field. List all the options that you want your users to be able to select. The values and labels don't really matter, they are up to you. For example:

  parameter: date_selector {
    type: string
    allowed_value: {
      label: "Last complete month"
      value: "last_month"
    }
    allowed_value: {
      label: "Last complete year"
      value: "last_year"
    }
  }

Step 2: Create the dimensions

This is the hard part. For each parameter value, we've got to create a corresponding dimension that performs the desired filter logic. In this case, I made these ones for "past complete month" and "past complete year". Remember that the SQL will differ by database! My test database was MySQL.

dimension: last_complete_month {
    type: yesno
    sql:  MONTH(${created_raw}) = MONTH(now()) -1 AND  YEAR(${created_raw}) = YEAR(now())  ;;
}

dimension: last_complete_year {
      type: yesno
      sql:  YEAR(${created_raw}) = YEAR(now()) -1 ;;
}

Step 3: Add a sql_always_where

Finally, it's time to put it all together. Add a sql_always_where parameter to the LookML explore definition. Connect the parameter values and dimension definitions using code like this:

sql_always_where:
  {% if inventory_items.date_selector._parameter_value == 'last_month' %}
   ${inventory_items.last_complete_month}
  {% elsif inventory_items.date_selector._parameter_value == 'last_year' %}
   ${inventory_items.last_complete_year}
  {% endif %}
;;

[aleishon]

Thank you, Sam! I’ll try it out and let you know how it goes! Thank you so much for your time; I really appreciate it.🤗

[aleishon]

Thank you, Sam!

I created the selector, and I understand it's an alternative, but it doesn’t fully solve my problem. I want the user to be free to select the months they need, and this option will depend on what I set up for them.

The ideal solution would be for the date range to allow me to enable or disable existing options based on the type of end user the dashboard is intended for.
For example, I’m attaching the options that are sufficient for the end users I’m developing the dashboard.

          

*I would like the 'is in range' option to allow selection by month in my case.

Having too many options just overwhelms them.
Thank you very much for your help.

Best Regards,

[sam8]

I hear you. The parameters example does force you to hard code each option, and really limits what a user can pick. 

I totally get it would be ideal to remove the excess options! I'm almost certain that would be a feature request though.

[aleishon]

I tried to solve it by creating the month and year selectors, but when I edit the filter, I don’t have the option to "Allow multiple filter values," so it won’t let me select multiple months or years.
                       

[sam8]

Clever workaround! But, yes, parameters don't support multiple filter values, so this would only let users view one month of data at a time.

I suppose you could add in options to the month selector like "Q1, Q2, Q3, Q4, All months"