Apply Min/Max to multiple columns in measure

Looker Q&A Looker
1

TLDR: How can I apply MIN/MAX in measures to multiple columns?

We have a table Person and 2 joined tables event1 and event2. Each of the event tables have date columns, lets call them ‘updated’ and ‘contacted’.

According to this article I can create a measure with min/max value for a single column as follows

measure: last_updated_date {  type: date  sql: MAX(${updated_raw}) ;;  convert_tz: no}

But how do I get the min/max value for each person.id from two columns (event1.updated and event2.contacted)?

2

You need to define the measure for each dimension. So if you have updated and contacted you’ll need four measures defined. Min and Max for each.

dimension: contacted_date {> type: date> sql: ${TABLE}.“CONTACTED_DATE”> }> > dimension: updated_date {> type: date> sql: ${TABLE}.“UPDATED_DATE”> }> > measure: min_contacted {> type: min> sql: {$contacted_date}> }> > measure: max_contacted {> type: max> sql: {$contacted_date}> }> > measure: min_updated {> type: min> sql: {$updated_date}> }> > measure: max_updated {> type: max> sql: {$updated_date}> }

3

Thanks!

That was my initial approach as well but then I got stuck at how to get the min from both columns, i.e. the min from min_updated and min_contacted.

4

Ah, I didn’t understand that as the objective.

If your database has the least/greatest function, that could do it.

dimension: person_min_date {

type: date

sql: least({$contacted_date}, {$updated_date})

}

5

That solved my issue :slight_smile:

Together with the explanation here https://database.guide/min-vs-least-in-mysql-whats-the-difference/, I was able to understand the difference between MIN and LEAST.

For the individual tables I’ve used

*measure: min_updated {*

_*type: date*_

<em><em>*sql: min(${TABLE}.updated_date) ;;*</em></em>

<em><em>_*}*_</em></em>