Coupling global dashboard filters and filter-only dimensions in Looker

Question

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.

The filters included are as follows:

• event_date - a date dimension implemented as a filter on the dashboard (which I would like to remove)
• 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 date_filter_base and date_filter_comp to be read into the event_date filter applied to the broader query where 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?

Answer 1

LookML is pretty flexible and you can acheive it depending on what you want. Answer lies in date type parameter of type datetime. Start with defining different parameters for base and comparison dates (Used 4 for better control)

  parameter: base_date_start {
    type: date_time
    description: "Start Date of Base"
  }
  
  parameter: base_date_end {
    type: date_time
    description: "End Date for Base"
  }
  
  parameter: comp_date_start {
    type: date_time
    description: "Start Date of Comparison"
  }
  
  parameter: comp_date_end {
    type: date_time
    description: "End date of Comparison"
  }

  dimension: event_date {
    type: date
    hidden: yes
    sql: ${TABLE}."DATE" ;;
  }

And now you can define independent filters to be applied to event_date (which can be removed from dashboard or very well set to be hidden now):

  filter: base_filter {
   type: yesno
   sql: ${event_date} between {% parameter base_date_start %} and {% parameter base_date_end %} ;;
  }

  filter: comp_filter {
   type: yesno
   sql: ${event_date} between {% parameter comp_date_start %} and {% parameter comp_date_end %} ;;
  }

  filter: base_or_comp_filter {
    type: yesno
    sql: ${comp_filter} OR ${base_filter} ;;
  }

Which in turn can be applied to measures as follows:

  measure: base_measure {
    type: sum
     sql: ${TABLE}.YOUR_COLUMN;;
     filters: [base_filter: "yes"]
  }

  measure: comp_measure {
    type: sum
     sql: ${TABLE}.YOUR_COLUMN;;
     filters: [comp_filter: "yes"]
  }

  measure: measure_valid_for_base_or_comp_ranges {
    type: sum
     sql: ${TABLE}.YOUR_COLUMN ;;
     filters: [base_or_comp_filter : "yes"]
}

When all three measures are selected in a visualization and parameters above selected as:

SQL generated as follows:

SELECT
    COALESCE(SUM(CASE WHEN ( ((TO_CHAR(TO_DATE(temp_test_datetime_params."DATE" ), 'YYYY-MM-DD')) between TO_TIMESTAMP('2023-11-27') and TO_TIMESTAMP('2023-12-02')) OR ((TO_CHAR(TO_DATE(temp_test_datetime_params."DATE" ), 'YYYY-MM-DD')) between TO_TIMESTAMP('2023-11-20') and TO_TIMESTAMP('2023-11-25')) ) THEN temp_test_datetime_params.YOUR_COLUMN ELSE NULL END), 0) AS "temp_test_datetime_params.measure_valid_for_base_or_comp_ranges",
    COALESCE(SUM(CASE WHEN ( (TO_CHAR(TO_DATE(temp_test_datetime_params."DATE" ), 'YYYY-MM-DD')) between TO_TIMESTAMP('2023-11-27') and TO_TIMESTAMP('2023-12-02') ) THEN temp_test_datetime_params.OUR_COLUMN ELSE NULL END), 0) AS "temp_test_datetime_params.comp_measure",
    COALESCE(SUM(CASE WHEN ( (TO_CHAR(TO_DATE(temp_test_datetime_params."DATE" ), 'YYYY-MM-DD')) between TO_TIMESTAMP('2023-11-20') and TO_TIMESTAMP('2023-11-25') ) THEN temp_test_datetime_params.OUR_COLUMN ELSE NULL END), 0) AS "temp_test_datetime_params.base_measure"
FROM "YOUR_SCHEMA"."YOUR_TABLE" AS temp_test_datetime_params

If you want to apply them everywhere consider using always_filter in your explore and utilize these filters accordingly.