Question

Filtered minimum date measure

  • 17 May 2022
  • 1 reply
  • 82 views

Userlevel 1

  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?


1 reply

Userlevel 1

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

Reply