filter on the most recent date from a table with many dates of data

Looker Q&A Looker
1

Folks,

I am new to Looker and trying to filter on the most recent date of the data that is loaded into a table used to create a simple set of results to display on a dashboard. Is there a straight forward way or do I have to use a work-around?

I can get to the very easy with BigQuery:

select acctNbr, customerName, statusDescription, currentBalance - balanceYesterday as dailyBalanceChange, date_data
from gold.certOfDepositsDataProduct
where date_data = (SELECT MAX(date_data) FROM gold.certOfDepositsDataProduct)
and abs(currentBalance - balanceYesterday) > 100000
and status = 1 ;

HELP!

2

Hey there, there are a few ways to do it, one of them is to create a custom dimension with a subquery (like the one you did).

This is an example on how to do it using BigQuery:

dimension: is_latest_date {
type: yesno
sql: ${date_raw} = (SELECT MAX(${[date_raw]}) FROM [project].[dataset].${TABLE}) ;;
}

And then use that as a filter

1 Like
3

fcharles,

Thanks for the solution to grab the latest set of data from BigQuery… Instead of doing this in Looker, we decided to create views in BigQuery for most recent day, end of week, end of month, end of quarter, and end of year in a similar fashion. I am a huge fan of letting BigQuery do all the heavy lifting and let Looker provide the intelligence in a form that is easily accessible.

Thank you again for your prompt response.

4

Good one, that’s on you to decide what works best for your maintenance and your users. You can also always make a native derived table for instance which would allow you to create those views in LookML without missing out on the semantic layer.

Whenever you would solve the problem with a subquery in SQL, one very potential solution in LookML is a derived table.

Quick sharing this article, it is about ELTs but also works on deciding between doing something as scheduled query, view or LookML: https://cloud.google.com/blog/products/data-analytics/why-use-both-lookml-and-elt-tools-in-your-data-analytics-stack