Introduction

To compare a metric filtered by several timeframes in the same Look or dashboard, we can use filtered measures. Filtered measures allow us to apply a hardcoded filter directly to a measure, as opposed to filtering the entire query.

If we had a limited number of timeframes we were interested in applying to a measure and comparing, we could simply define a couple of filtered measures with hardcoded timeframes (for example, "this year" and "last year"), and display them in Look or dashboard. However, if we wanted to make these timeframe comparisons dynamic, we can take this approach a step further, and define filtered measures that listen to timeframes specified in the filter UI, using templated filters.

Pattern Overview

On a high level, this approach involves 3 components. For each timeframe, we define a templated filter of type: date that will appear as a filter-only field on our dashboard or Look. This templated filter is applied in the LookML to a yesno dimension, so that when a value is selected in the templated filter, it will cause the yesno dimension to return "yes" for records where the filter condition is met.

Lastly, we will apply this yesno dimension to our measure, requiring that the yesno dimension value = "yes". This will perform our measure's aggregation only over records that meet the specified timeframe condition in our filter.

On the frontend, this pattern will produce the following set up:

The Code

The following LookML assumes we have a dimension group with a raw timeframe named created_raw:

  dimension_group: created {
    type: time
    timeframes: [
      raw,
      time,
      date,
    ]
    sql: ${TABLE}.created_at ;;
  }
  

Given this, the LookML set up for comparing a count measure filtered by 2 dynamic timeframes will look like this.

  ## filter determining time range for all "A" measures

  filter: timeframe_a {
    type: date_time
   }

  ## flag for "A" measures to only include appropriate time range

  dimension: group_a_yesno {
    hidden: yes
    type: yesno
    sql: {% condition timeframe_a %} ${created_raw} {% endcondition %} ;;
  }

  ## filtered measure A

   measure: count_a {
    type: count
    filters: [group_a_yesno: "yes"]
   }

  ## filter determining time range for all "B" measures

   filter: timeframe_b {
    type: date_time
   }

  ## flag for "B" measures to only include appropriate time range

  dimension: group_b_yesno {
    hidden: yes
    type: yesno
    sql: {% condition timeframe_b %} ${created_raw} {% endcondition %} ;;
   }

   measure: count_b {
    type: count
    filters: [group_b_yesno: "yes"]
   }
  

We can repeat this process for as many timeframe comparisons as we need. Note that filtered measures do not automatically add a filter to the overall query. If we want to limit the entire query to within the specified timeframes, we can create the following yesno dimension, and filter it to "yes" in our Looks/dashboards:

  dimension: is_in_time_a_or_b {
    group_label: "Time Comparison Filters"
    type: yesno
    sql:
      {% condition timeframe_a %} ${created_raw} {% endcondition %} OR
      {% condition timeframe_b %} ${created_raw} {% endcondition %} ;;
  }

This will prevent the database from scanning more data than needed for this query, and may help with performance and query cost.