Yesno for data

Looker Q&A Looker
1

Dear Colleagues, I have a question.

I have column with string. I do like this:

dimension: main_table_boolean {
    type: yesno
    sql: ${TABLE}.table_name LIKE '%ord%';;
  }

  dimension: main_table {
    type: string
    sql: case when ${main_table_boolean} then 'main' else 'not_main' end ;;
    }

It gives me answer:



Missing Data Main Table

Missing Data Average Percent
1 not_main 7.404985591339665
2 main 5.748898156787999

Perfect!

But if I need to do the same with date column it does not work. I do like this:

dimension: last_date_boolean {
    type: yesno
    sql: ${TABLE}.last_date_in_batch = MAX(${TABLE}.last_date_in_batch);;
  }

  dimension: new_old_data {
    type: string
    sql: case when ${last_date_boolean} then 'new_data' else 'old_data' end ;;

  }

And Looker gives me error: aggregate functions are not allowed in GROUP BY

I suppose it means MAX function. Please advice is there a way to avoid such a behavior of Looker?

Many thanks!

Sergio