Apply Min/Max to multiple columns in measure

[MaxPeters]

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`)?

[Chris_Tinnon]

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}
}

[MaxPeters]

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.

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`.

[Chris_Tinnon · Accepted solution]

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`.

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})

}
 

[MaxPeters]

That solved my issue 🙂

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

  sql: min(${TABLE}.updated_date) ;;

}