Dynamically choose the table using Date filter values without adding new filters/parameters.

[swathikiran]

Hello All,

I have a derived table for monthly data and a dashboard based on that. But when users are trying to fetch results for the past days(in the created date filter field) they are receiving No results if (current date - days is less that 01st of every month). This is because the monthly derived is grouped and stores the values on 01st of every month. 

Example: When user tries to fetch results for past 20 days - No Results

But when they query for past 28 days then there is aggregated data displayed - because 01/01/2021 has aggregated monthly value.

Solution I tried:

I am unable to use aggregated awareness because my explore is using joins and aggregated table is not considering those joins. So it is not a feasible option for us. Also using joins is creating DISTINCT in the query and aggregated awareness doesn't go to aggregated table when there is a DISTINBCT in the query,

Also, I am unable to restrict the filter options to month only. Because, looker doesn't allow that,  if the type: date/time then the dropdown in the filter has days/hours/years...and so on.

Solution am trying:

I have created a bigger aggregated date table. I want the results (Especially count measure) to be fetched from date PDT when user uses days in the filter. If the user uses months or years then I want looker to use the monthly PDT.

To achieve this, I have to manipulate the sql_table_name dynamically using the filter value.
I am unable to catch the filter value to use that in the if else condition to select my table.

Can anyone please suggest if this is viable solution or is there any better solution that I can get this done.

Please Note - I need to avoid adding any new parameters or fields for users to filter.

My looker version is 7.14 and I am using Redshift.

Thank you,

Regards,

Kiran