LinkedIn
Twitter
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.
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
- The order_by_field parameter allows us to sort the order of a dimension by another dimension.
- In this case we want to sort by date, so we can add order_by_field: your_original_date_field to fix that:
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!
