liquid condition / endcondition / _is_filtered when using AND/OR filters

[dan-man]

Until the Feb 2024 release of Looker, it was possible to use the liquid {% condition dimension_name %} dimension_name {% endcondition %} syntax, to work out if a row matches a user's filter independently of the main auto-generated query logic.

This was really useful when combined with window (aka analytic) functions in a derived table's  definition, because you could dynamically perform these windowed calculations and treat them as first-class columns from the point of view of everything else in the view.

With the new AND/OR filters, it's no longer possible to do this, or rather it works up until the point that the user goes beyond the basic AND condition, and then although `_is_filtered` seems to be true still, the condition comes out as `1=1`. To be fair, this was probably also an issue historically when using the advanced filter widget, but few users even considered that whereas the AND/OR stuff is much more visible to people.

What should happen in such circumstances, given you can no longer define the filter for a given dimension in isolation? Perhaps it should give an error when you try and run that. Alternatively, there could be an `_is_in_complex_filter` in liquid so that the developer can decide what should happen, such as using an ERROR() statement in sql. It would be even better if there was some way to access the full condition for the table rather than having to do it one dimension at a time, although even then it's entirely possible the complex filters span multiple tables.

If there are better ways of handling the wider use case of using window functions on dimensions in looker (besides pre-computing, or relying on table calculations which are fairly limited and painful to use frequently), do let me know! 

In terms of a use case for window functions:

Imagine you want to allow users to filter products in some fairly generic way (such that you can't pre-calculate metrics for all possible variations of filters), and then with these filters applied to products, be able to visualise the average value of the last purchase for each customer by country, or the average running total per customer by country over time.  The problem is you can't pre-compute a flag for which row is the "last" because it depends on the filters, and similarly for running total this also depends on the filters. In this example the lookml below would work for basic AND filtering, but once you get to something like this - "(Product Launch Date: < 2019 AND Manufacturer: Acme) OR (Manufacturer: Other Corp)" - then it stops applying the filters entirely (without any warning to the user).

---

example derived_table.sql:

WITH filter_flag AS (
    SELECT
      *,
      {%- if dimension_one._is_filtered %}
          ({% condition dimension_one %} dimension_one  {% endcondition %})
      {% else %} TRUE {% endif %}
      /* AND similar for dimension_two, _three.. */ 
      AS row_passes_filter
    FROM my_dataset.company_timeseries
)
SELECT
    *,
    SUM(IF(passes_filter, amount_usd, 0))
      OVER (company_window) AS amount_usd_cumulative_filter_aware,
    -- and some other similar things such as filtered row numbering etc.
FROM filter_flag
WINDOW company_window AS (PARTITION BY company_id ORDER BY date ASC)

Edit - another idea:

Perhaps lookml could let you define allowed combinations of filters and dimensions somehow, e.g. in the above, the amount_usd_cumulative_filter_aware dimension would say it only support simple filtering by dimension_one and not complex filtering involving anything else or any other simple filter (maybe a niche requirement, but would make this kind of derived table construction safer in general).