Filtered minimum date measure

kuopaz
Participant IV

  measure: min_this_date {
    type: date
    sql: min(${this_date}) ;;
    filters: [other_field: "-NULL"]
  }

This will error as filters are not allowed on measures of type date. Filters are allowed on measures of type min, but this does not work for dimensions of type date that has to have the min in the sql. How do I work around this?

0 1 651
1 REPLY 1

kuopaz
Participant IV

I think this will do it?

# convert this_date to a number (day count from 1900-01-01 in SQL Server)

# this allows the use of min and filter

measure: min_this_date_as_number {
    type: min
    sql: datediff(day, 0, ${this_date}) ;;
    filters: [other_field: "-NULL"]
}

# now convert back from number to date

measure: min_this_date {
    type: date
    sql: dateadd(day, 0, ${min_this_date_as_number}) ;;
 }

Top Labels in this Space
Top Solution Authors