Parameters & Filters - Dynamically changing default date to latest available date

[palaniappan0212]

Hi all,

Could you please let me know if there is any way to show the filter/parameter value at top of the Looker to default to the latest date available in the backend data table ? 

Need to show it as a date itself. The options like using strings as default value - “Today”/”Yesterday”/”Latest date” does not work in my case as the dashboards users are from different timezones and so hardcoding these strings cause confusion depending on when they access the dashboard.

Thank you in advance.

[Dawid]

How will Looker know what’s the latest? You either need to build it to your data model  (have a column that is the same for all rows and show those that equal to this date) but that can amount to a bit of data if your table is huge.

Another way is to have a derived table that outputs the date of interest and join it to the explore

[palaniappan0212]

Hi Dawid.. Thank you for the quick response..

Yeah, I have done that as well.. Using a derived table, I have added a new column which has only the latest date.

And also I have an index column which always has the value 1 for the latest date.

Even if I create a yesno filter and provide a default filter for latest date column in the explore, it shows only the latest date (not by default on the filter but on clicking the filter, only the latest date is shown). With this option, user does not have the option to select older dates.

Please let me know if I am missing anything here.

Thanka again!

[Dawid]

That would depend on how you built it and what your end result you want. Share some LookML/screenshots and perhaps we can help further

[palaniappan0212]

Sure.. PFB the current structure in the backend. Date column is coming from the actual table while other columns are created through derived table/LookML

and this is how the front end parameter looks like now (Date has been formatted to be a string here)

The issue here is we are able to configure only a static default date value. When the table gets updated with the next day’s data, the filter will still show current date by default and requires manual intervention to update to latest date.

If we include latest date flag, then we have to manually click and select the latest date. Default value set up for an older date does not get updated and so requires a manual click to update.

[Dawid]

Why do you need the date filter. Why isn’t Latest Date Yes/No is not enough?

[palaniappan0212]

Users need to have the ability to select older dates as well and so we need to have to date filter ideally.

For almost the same reason, Latest date Yes/No is not enough. Even if we have latest date filter, users need to do 3 clicks

1. Click No in Latest date Yes/No 
2. Click on the dropdown for Date filter
3. Select the required date

Having just a date filter (where we already have the sorted list of dates) where the default value is the one at the top would be a much simpler and clean solution to have.

[upadhyay_tarang]

You can trying putting the Latest date Yes/No dimension in the sql_always_where in model, this way it will return the latest date & will always be hidden from user.

[palaniappan0212]

Hi Tarang Upadhyay. Thank you for the response.

Unfortunately, what happens with this sql_always_where inclusion is that, the front end filter will have only the latest date value and the ability to select older dates is gone.

Also, in parameters/filters, there is no option to dynamically get latest date inside it. For example, lets say, latest date is 3/28 and tomorrow once 3/29 data comes up, Looker does not automatically pull in that value. It would still show 3/28 and we have to manually update default value to 3/29. (Similar case as the 3rd screenshot I have attached above)

[Dawid]

I was thinking about adding a filter field type:string with this date column. String would mean the filter would be just a selection of dates but I can’t figure out how to get it to order with DESC

[palaniappan0212]

If it helps, in the second screenshot above, I have formatted date field as a string and have ordered it DESC using the index column that I created (available in the first screenshot). 

[matt_jackman-16]

I’ve encountered the same limitation to try to default to the latest day but allow users to override this to view past days. The tricky bit is getting it to be as user friendly as possible.

In speaking with Looker support and some Looker implementation specialists, there isn’t a way to dynamically set the default value (e.g. default_value: ${latest_load_date.date})

I developed, as you did, a derived table to find the maximum date. From there, I played with the idea of setting a ‘2000-01-01’ default date, knowing that that would be too early for the data set and using liquid to select the max date it if was still that default date and if it wasn’t use the date input via the ‘date selection’ parameter. This simplified the user experience, but had the potential to cause confusion when users pulled up the report. It’s definitely a usable solution though.

In the end, I went with what you explained above, using a ‘Latest Day’ selector of Yes/No and having the user pick the date accordingly. It makes it a bit easier to schedule reports and is fairly straight forward.

If Looker could implement 2 things, it would make this scenario and many others much more functional:

1. A true dynamic default value for parameters
2. Dynamic Dashboard filters that hide / show filters based on the values of others. So if the Latest Day parameter is Yes, no date field appears, otherwise it does.