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.
dimension: week_start {
type: string
sql: LEFT(CAST(DATE_TRUNC(${TABLE}.date, WEEK(SUNDAY)) AS STRING),10) ;;
}
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.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.