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.
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
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!
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
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.
Why do you need the date filter. Why isn’t Latest Date Yes/No is not enough?
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
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.
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.
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)
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
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).
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:
I have worked out a somewhat hacky solution to allow 1 Looker Dashboard Filter that defaults to latest date but still allows the user to select from the other available dates, using some of the ideas in this thread around casting the date field to string and an idea here on how to sort that string by descending dates: https://www.googlecloudcommunity.com/gc/Modeling/order-by-field-descending/m-p/704329
I’ll first paste the screenshot of the solution and the code so you can use it quickly if you’d like, then explain a bit more how it works further down in the post if you are curious:
dimension: latest_created_date_filter {
type: string
order_by_field: date_sorter
sql: CASE
WHEN ${created_date} = (SELECT MAX(${created_date})
FROM `thelook_ecomm.order_items` AS order_items)
THEN 'Latest Order Date'
ELSE CAST(${created_date} as string)
END ;;
}
dimension: date_sorter {
hidden: yes
type: number
sql: DATE_DIFF( ${created_date} , '1900-01-01', DAY) * -1 ;;
}
Now for how it works:
Step 1: Create a case when dimension of type: string that we can filter on
We want a case when statement return ‘Latest Date’ if the date matches the latest date in the dataset, and otherwise returns the absolute value of other dates (for example: ‘2025-03-01’)
To calculate the latest date of the dataset we just use a simple SELECT MAX(your_date_field) subquery inside the first WHEN condition and ELSE just return the date as a string:
dimension: latest_created_date_filter {
type: string
sql: CASE
WHEN ${created_date} = (SELECT MAX(${created_date})
FROM `thelook_ecomm.order_items` AS order_items)
THEN 'Latest Order Date'
ELSE CAST(${created_date} as string)
END ;;
}
Now when we add this to a filter in the explore we can see that the logic works, but the sorting is all wrong (because it’s sorted as a string):
Step 2: use order_by_field to sort based on date
dimension: latest_created_date_filter {
type: string
order_by_field: created_date
sql: CASE
WHEN ${created_date} = (SELECT MAX(${created_date})
FROM `thelook_ecomm.order_items` AS order_items)
THEN 'Latest Order Date'
ELSE CAST(${created_date} as string)
END ;;
}
Unfortunately Looker filter suggestions sort descending which doesn’t work for us as you can see here:
Step 3: Use a helper dimension to flip the sort and user order_by_field with that helper dimension
While there’s no parameter to force filter suggestions to sort ascending, we can just use some clever SQL in a new dimension to convert our dates to numbers in the order we want (And add hidden:yes to not clutter our explore)
dimension: date_sorter {
hidden: yes
type: number
sql: DATE_DIFF( ${created_date} , '1900-01-01', DAY) * -1 ;;
}
I completely stole this trick for my SQL dialect (BQ) from the clever people who answered this post, see their post for more details and other SQL dialects
Finally we change our order_by_field parameter for our latest_created_date_filter field to that date_sorter helper dimension and we are good to go!
dimension: latest_created_date_filter {
type: string
order_by_field: date_sorter
sql: CASE
WHEN ${created_date} = (SELECT MAX(${created_date})
FROM `thelook_ecomm.order_items` AS order_items)
THEN 'Latest Order Date'
ELSE CAST(${created_date} as string)
END ;;
}
dimension: date_sorter {
hidden: yes
type: number
sql: DATE_DIFF( ${created_date} , '1900-01-01', DAY) * -1 ;;
}
If you want to create a dashboard from this, just make sure to set the filter on that latest_created_date_filter dimension you created, and lock in the default value for the filter as ‘Latest Order Date’
I hope this helps! If it does what you need please mark this as solved so other folks can find the answer quickly!
Thanks for posting this @looker_ant , I thought about this as a solution for my similar issue.
It was nice to see the full implementation before trying to solve it myself!
Thanks @looker_ant this is a good hack, helped for my use case
glad to help!
happy to help, you are welcome!
