Filter Max Date by different timeframes

[EliasArg]

Hi! I have an unusual use case that i would like to resolve. 

I have a BigQuery table that has information week by week (let's say, it's on mondays). From that BigQuery table I'm creating a dashboard in Looker and I want to give the user the opportunity to filter by "Week", "Month", "Quarter" or "Year" but with Button Toggles, and whenever the user selects "Month", "Quarter" or "Year", in another filter he can filter with its posibles values, where the posible values are the latest date. For example: for month would be 03/2x/2024, 04/2x/2024, étc. For "Quarter" would be, "03/2x/2024", "06/2x/2024", étc. 

I know the first restriction I have is the type of the dimension, since the Button Toggles are only available for strings but I think i can overpass that type problem with parameters. 

The possible solutions I have thought are: 

1. Create a derivated table with a sql query like: 

  SELECT DATE_TRUNC(CAST(payment_date AS DATE), MONTH) as month, max(payment_date)
    FROM table
    GROUP BY month

CONS: I'm only getting the max date for each month and if I want to check other weeks in the month I can't.

2. Denormalize my bigquery table and persist in different columns (strings) the timeframes I need. 

CONS: I need Week, Month, Quarter and Month, this means I'm going to need 4 rows minimum for each row. 

[robertcarr]

Yeah I think the idea of having an overall dates table and joining that back into your model for your filter use case would make sense. You can tie your two filters together then.

SELECT EXTRACT(YEAR FROM day), EXTRACT(WEEK FROM day)
FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE('2015-06-01'), CURRENT_DATE(), INTERVAL 1 WEEK)
) AS day

 You could use something like the above to create your initial dates table. 

Docs on linking filters:

https://cloud.google.com/looker/docs/filters-user-defined-dashboards#setting_up_linked_filters