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`)?
Solved! Go to 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})
}
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}
}
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`.
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})
}
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) ;;
}