Configurable date filters with mixed metric aggregation behavior in dashboards

Looker Q&A Looker
1

Hello team

I’m currently working in LookML and using a centralized timeframe filter in my dashboards with values like:

  • lw (last week)

  • mtd (month to date)

  • qtd (quarter to date)

  • ytd (year to date)

This works well for filtering the dashboard overall, but I have a challenge:
For example:

  • Metrics like sales should be summed over the timeframe.

  • Metrics like headcount should show the latest snapshot (e.g., the most recent week), not summed.

It would be very helpful if Looker supported per-metric behavior rules for date filtering—something like:

  • If timeframe = ytd, then:

    • Sum metrics A and B

    • Show latest snapshot for metrics C and D

      Is there a way to do this today? If not, I’d love to see this as a feature in future Looker updates. Thanks!

2

I’m pretty sure you could put something together that would achieve something close!

There are two steps to my idea:

  1. Get the timeframe filter value and reference it in LookML
  2. Make a custom measure with different behavior based on filter value

Step 1: Get the filter value

The easiest way to get this would be the _filters[‘view_name.field_name’] Liquid variable. But, according to the Liquid reference, we can’t call this in sql parameters of a LookML field, so this may not be robust enough for our needs. Can you change the timeframe to a parameter instead? Then we can call it using the parameter parameter_name liquid variable.

Step 2: Create a conditional measure

Next, we’ll write a measure in LookML that listens to this parameter and returns the appropriate value.

measure: dynamic_measure {
  type: number
  sql: CASE WHEN (% parameter timeframe %) = 'ytd'
            THEN ${metric_a} + ${metric_b}
          WHEN (other condition...)
            THEN (other result...)
          END;;
}

I hope this helps as a starting point!