Getting Nth row/value in a measure

lookml
low_priority
done
reply
(Jess Venticinque) #1

Hello!

I have added a measure for the minimum of a value as follows:

  measure: min_start_time {
   type: date
   sql: MIN(${TABLE}.start) ;;
   drill_fields: [detail*]
  }

However, I also want to be able to find the Nth minimum. Is there a way to do this with built-in functions or do I need to write a sql query?

0 Likes

(Izzy) #2

In general, this sort of thing is best accomplished with window functions in SQL queries, like you mentioned. If you’d be ok with a less permanent approach you could do a table calc! Either a super simple one where you order by start time and return row() which might not be robust, or you can do some extra-crazy wizardry to do an actual window function in table calculations.

@chris.seymour knows the ancient incantations and can maybe share them!

0 Likes