MIN / MAX Date in Custom Measures / Table Calculations / Functions

Dawid
Participant V

I know this topic has been covered few times but I wanted to start the conversation again. Is there any chance we can expect it to happen? I mean support min/max for date types in functions, custom measures, and table calculations.

Creating MIN/MAX values in LookML for each possible timestamp is just “evil” not to mention we can’t do that in System Activity.

I would also like to know why this can’t / won’t / hasn’t been implemented yet. Are there some restrictions we are not aware of?

12 3 3,851
3 REPLIES 3

Seconding this! Would love this for system activity, but also so we can avoid having users need to put in a ticket every time they need to do a min/max on a timestamp.

IanT
Participant V

System activity should be open to extend as well.

I just tried this as a thought experiment -- here’s what I came up with to get a max date in a table calc:

add_days(max(diff_days(now(), ${twd_order.close_dte_date})), trunc_days(now()))

It gets the maximum number of days between today and the given date (which for today, would be 0, for tomorrow, 1, for yesterday, -1, etc). Looker permits the max() because it’s on a number. Then you add that number of days to today’s date.

Of course (a) this is a silly thing to need to do for something so simple, and (b) it won’t work for custom measures, and (c) it doesn’t help for System Activity. But thought I’d share in case it helps anyone.



… To be honest, I actually came to this solution first, and then figured out I was overcomplicating things, haha:

add_days(mod(max((extract_years(${twd_order.close_dte_date}) * 365) + diff_days(trunc_years(${twd_order.close_dte_date}), ${twd_order.close_dte_date})), 365), date(round(max((extract_years(${twd_order.close_dte_date}) * 365) +(diff_days(trunc_years(${twd_order.close_dte_date}), ${twd_order.close_dte_date}))) / 365, 0),1,1))

Top Labels in this Space
Top Solution Authors