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

[Christel_Ilaka]

Could you give me more detail on the logic that should be used to select the DAYS or MONTH PDT? 

Let say that we’re on Feb. 2nd and the user filter on the last 5 days what table should be used? 

Should the Month Pdt be used only when the 1st day of the month is between the selected dates? like use the Month Pdt if the user select 2021-01-20 to 2021-02-02 (since 2021-02-01 is in this range)

We should be able to reach your end goal but it’ll require some Liquid implementations.

[swathikiran]

Hello Christel,

Logic is as below:

For (is in the past) option

if the selection is either or days or weeks.

then goto Date PDT.

else goto Monthly PDT.

For (is on the day) option

goto Date PDT

For (is in the range) option

if both the dates are of 01st days of the month

goto Monthly PDT

else goto Date PDT

and so on…

So logic is purely dependent of using Monthly PDT as much as possible for better performance.

However, for accuracy comes first- so fetch from date table when user wants data of several range of dates. 

I am currently implementing a solution creating a new view using RDT on top of these both PDTs.
But I need to test and ensure that it doesn't create any complications or performance issues.

Please let me know if there is any better way or better solution.

Also let me know if you need any other information.

Thanks,

Regards,

Kiran