Filtering an additional field based on a dashboard filter

[Ryan_PS]

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”.

[Ryan_PS · Accepted solution]

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.