How can I use linked filters with dates in the dashboard?

[Pieter_DeVries]

For a use-case such as, I want to:

Choose the start of a week in one filter and then choose the days of that week in another

We can get that working using dashboard filters like below.

1. Choose the week starting date
   

   

2. Choose which day of the week.
   

   

The magic going on in the background, is fairly simple.

1. We will be using our main view file for the week start field, and a derived table to generate the days within that week, or month or however you want to structure your data.

2. In the main view, we create our week_start dimension:

   dimension: week_start {
         type: string
         sql: LEFT(CAST(DATE_TRUNC(${TABLE}.date, WEEK(SUNDAY)) AS STRING),10) ;;
       }​
   

   I am using BigQuery standard sql, but other sql dialects should have similar variations. We use a type: string because dashboard filters need to be type string in order to use linked filters. the WEEK(SUNDAY) can be changed to (MONDAY) if your weeks start on monday instead.

3. In our derived table I use the below code:

   view: week_days {
     derived_table: {
       sql:
         SELECT date
         FROM ${<main_view_name>.SQL_TABLE_NAME} as <main_view_name> ;;
     }    
     dimension: day_choice {
       type: string
       sql: LEFT(CAST(DATE_TRUNC(${TABLE}.date, DAY) AS STRING),10);;
       }​
   

   This derived table could be sql based or an NDT, either works.

4. Finally, we join our derived table view to our main view file in the explore:

   explore: <main_view_name> {
     join: week_days {
       sql_on: ${week_days.day_choice} = CAST(${<main_view_name>.<date_field>} AS STRING) ;;
       relationship: many_to_one​
   

   This allows the two views to be linked, and enables our linked filters. If you don’t want the day_choice field visible in explores, you can add hidden: yes to it.

5. Next, we just need to link those filters together in the dashboard, and they can be used in sync.