Calculating totals when using GREATEST - interesting case

Dawid
Participant V

An interesting case I discovered today, which may be useful to make a note of. 
 

Let’s say I have three measures build like this:


measure: total_expected {
type: sum
sql: ${value} ;;
filters: [filter: "Yes"]
}

measure: total_received {
type: sum
sql: ${value} ;;
filters: [other_filter: "Yes"]
}

measure: total_committed {
type: number
sql: GREATEST(${total_expected}, ${total_received}) ;;
}

Now when I turn on the totals, the query for totals compares GREATEST(SUM(expected), SUM(received)), which of course makes perfect sense based on the implementation but for a long time I was wondering why the row isn’t evaluated first and the summed SUM(GREATEST(expected, received).

In order to do that my measures would have to be dimensions and then total_committed could be changed to a measure of type sum. Or have the data in table calculation (which is not convenient for a table vis as another columns that repeats the same value)

Theoretically obvious but I did spend 15 minutes looking at the totals and going WTF 😄  

The main culprit here is the lack of OR in filters, so for now I opted in for another approach:


 

  measure: total_committed {
    type: sum
    sql: IFF(NOT ${filter} OR ${other_filter}, ${value}, NULL) ;; 
  }
1 0 178
0 REPLIES 0
Top Labels in this Space
Top Solution Authors