Using filter-only dimensions for table level (WHERE clause) filter in Looker

Looker Q&A Looker
1

I have a dashboard that utilizes filter-only date dimensions to compare two ranges (base and comparison). I also have a more general global date filter which, for my dashboard use case, is redundant. I would like to have the global date filter use the filter-only dimensions in a dashboard.

There are three filters included:

  • filter on event_date - a date dimension implemented as an always_filter on the dashboard (which I would like to remove or use the following filters to satisfy)
  • base range date filter (dt_a) - a filter applied to measures used for comparison
  • comparison range date filter (dt_b) - a filter applied to measures compared to base measures

The dimensions and filters are defined as follows:

# Global calendar date
dimension: event_date {
    sql: ${TABLE}.event_date ;;
  }

# Filters and filter-only dimensions
filter: dt_a {
    label: "Date filter (base)"
    type: date
  }
  dimension: group_a_yesno {
    hidden: yes
    type: yesno
    sql: {% condition dt_a %} timestamp(${event_date}) {% endcondition %} ;;
  }

  filter: dt_b {
    label: "Date filter (comp)"
    type: date
  }
  dimension: group_b_yesno {
    hidden: yes
    type: yesno
    sql:  {% condition dt_b %} timestamp(${event_date}) {% endcondition %} ;;
  }

I have explored liquid parameters in the table definition as well as filter setting in the dashboard itself.

Ideally, I would like the dt_a and dt_b to be read into the event_date filter and applied to the broader query so that the WHERE clause reads the filters applied to each of the measures view_count_a and view_count_b.

For example, comparing two count_distinct measures whose date filters are used in as OR:

select
    COUNT(DISTINCT CASE WHEN ((( timestamp(view.event_date) ) >= (TIMESTAMP('2023-11-20 00:00:00')) AND ( timestamp(view.event_date) ) < (TIMESTAMP('2023-11-25 00:00:00')))) THEN ga4_view_bq.user_pseudo_id ELSE NULL END) AS view_count_a,
    COUNT(DISTINCT CASE WHEN ((( timestamp(view.event_date) ) >= (TIMESTAMP('2023-11-27 00:00:00')) AND ( timestamp(view.event_date) ) < (TIMESTAMP('2023-12-02 00:00:00')))) THEN view.user_pseudo_id ELSE NULL END) AS view_count_b,
from ...
where
    ((( timestamp(view.event_date) ) >= (TIMESTAMP('2023-11-20 00:00:00')) AND ( timestamp(view.event_date) ) < (TIMESTAMP('2023-11-25 00:00:00'))) OR
    ((( timestamp(view.event_date) ) >= (TIMESTAMP('2023-11-27 00:00:00')) AND ( timestamp(view.event_date) ) < (TIMESTAMP('2023-12-02 00:00:00')))
;

Is there a way to implement this in LookML?