Filtering an additional field based on a dashboard filter

Looker Q&A Looker
1

Is there a way to filter a field based on a dashboard filter? I’ve been checking out templated filters, but I’m not sure if it covers this use case.

Scenario:

  • Dashboard has a date filter that user can select, but we partition our table by week_date
  • week_date is simply Monday’s date for a given week: DATE_TRUNC(${date}, WEEK(MONDAY))

So when the users sets the date via a dashboard filter to “2022-9-2”, we not only want the table to be filter the table’s “date” to the inputted date, but we also want it to filter the week_date to “2022-8-29”.

2

After researching this, I don’t believe this possible, at least not for a date filter. Instead, I am going to implement 1 of the below solutions:

  1. Add a 2nd date filter that has only “week” as a timeframe option (see below screenshot). However, this doesn’t seem to work because Looker’s date filter functionality doesn’t support selecting specific weeks; even if it did, it easily allows the user to to convert back to date because all date filter options are always available to a user (using “advanced”, “timeframes”, etc.).

  2. Add a 2nd string filter for week. This would allow for the restriction of selectable values. It could either simply display filter suggestions like normal, but this would have gaps since we don’t have data for every week.
    Since we still want a user to be able to select a week regardless if we have data for that week or not, we’ll use suggest_dimension to reference another new dimension which will have something like this in the sql:

    SELECT CAST(week AS STRING) AS week FROM UNNEST(GENERATE_DATE_ARRAY(DATE_TRUNC('2000-1-1', WEEK(MONDAY)), CURRENT_DATE(), INTERVAL 1 WEEK)) AS week ORDER BY 1 ASC

    This generates a list of Monday dates since January 3rd, 2000 (the 1st Monday of the year 2000). This dimension will also have both can_filter and hidden set to no.