Syntax for "less than or equal to" in templated filter

Looker Q&A Looker
,
1

I am trying to create a templated filter that will only show data on or before a given date. i.e. a filter that evaluates to WHERE report.date <= filter_date.

The documentation on templated filters says “Because templated filters return a logical expression, you can use them with other logical operators and logical expressions that are valid in the SQL WHERE statement” so it should be possible. But every syntax I have tried returns an error.

I’m aware that it’s possible to just do {% condition filter_date %} report.date {% endcondition %} and then handle the specifics of the filtering in dashboard filters, but that will not work for my purposes.

What would be the correct syntax for this?

2

Assuming you are using a PDT, you could use a parameter. Example:

  parameter: max_date {
    type: date
  }

  derived_table: {
    sql: 
         select *
         from sometable
         where the_date <= {% parameter max_date %};;
  }

  -- Or you could add an if to still show a value if there is no selection, like this:

  derived_table: {
    sql: 
         select *
         from sometable
         {% if max_date._parameter_value != 'none' %}
         where the_date <= {% parameter max_date %}
         {% endif %};;
  }