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?
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 %};;
}