How to determine the minimum or maximum date with ...

lauren_boltz · 02-17-2021 01:59 PM

Authors: The Looker Team

The problem

We want to find the minimum or maximum date in our Explore results, but the MIN and MAX table calculation functions are only compatible with numbers.

The Looker team outlines some ways below - read on!

The solutions

Below are a few ways to calculate the minimum or maximum date in an Explore with table calculation functions and operators. Make sure to replace the date field ${orders.created_date} with your own date field.

If you want to null out every other row, you can compare the diff_days between your date field and now().

The following obtains the MAX of a date, but can also be used to calculate a MIN of a date:

if(
 diff_days(now(),${orders.created_date}) =
	max(diff_days(now(),${orders.created_date}))
	,${orders.created_date},
null)

This calculation displays the MIN of a date for an entire column:

index(${orders.created_date}, match(min(diff_days(${orders.created_date}, now())), diff_days(${orders.created_date}, now())))

You can also hash the date into a number and find the maximum of that:

max( extract_days(${orders.created_date}) + 
 100* extract_months(${orders.created_date}) + 
 10000* extract_years(${orders.created_date}))

To turn that max hash calculation, here named ${max_hash}, back into a date, use:

date(floor(${max_hash}/10000), floor((${max_hash} - 
 (floor(${max_hash}/10000)*10000))/100), (${max_hash} -
 (floor(${max_hash}/100)*100)))

Below is another way to turn the ${max_hash} calculation back into a date:

to_date(
 concat(
 substring(
 concat(max(to_number(replace(concat(${orders.created_date}, ""), "-", ""))), ""), 0, 4),
 "-",
 substring(
 concat(max(to_number(replace(concat(${orders.created_date}, ""), "-", ""))), ""), 5, 2),
 "-",
 substring(
 concat(max(to_number(replace(concat(${orders.created_date}, ""), "-", ""))), ""), 7, 2))
)

Now you can calculate the minimum or maximum date in your Explore results!

MichelleOgburn

Will Looker add Min/Max dates as a standard to the measures? It seems like a simple thing to add to the Add Custom Field

adeel_nazir