Hello, I have two following dimensions:
dimension_group: date_attribution {
type: time
timeframes: [
raw,
date,
week,
month,
quarter,
year
]
convert_tz: no
datatype: date
sql: ${TABLE}.date_attribution ;;
drill_fields: [drill_fields*]
}
and
dimension: maturity_flag {
hidden: no
type: number
sql: DATE_DIFF(CURRENT_DATE(), ${TABLE}.date_attribution, DAY) ;;
}
I also have the following measure:
measure: mature_CPPU_d7 {
hidden: no
type: number
sql: ${sum_cost}/NULLIF(${converting_d7}, 0);;
value_format_name: usd
drill_fields: [game]
}
What I am trying to achieve is to select only the dimension date_attribution (date for example) and the measure mature_CPPU_d7 but display the latter in different ways. If maturity_flag >= 9, then I want the value of the measure displayed. Otherwise, I want the value and * displayed.
If I do this:
measure: mature_CPPU_d7 {
hidden: no
type: number
sql: ${sum_cost}/NULLIF(${converting_d7}, 0);;
value_format_name: usd
html: {% if maturity_flag._value >= 9 %}
<a href="#drillmenu" target="_self"> {{ rendered_value }}
{% else %}
<a href="#drillmenu" target="_self"> {{ rendered_value }} *
{% endif %}
;;
drill_fields: [game]
}
it gets me what I want, some values have * and some do not. The problem is that Looker also includes the maturity_flag in the group by clause. So following the solution by Looker, I do this:
measure: mature_CPPU_d7 {
hidden: no
type: number
sql: ${sum_cost}/NULLIF(${converting_d7}, 0);;
value_format_name: usd
html: {% if row['maturity_flag._value'] >= 9 %}
<a href="#drillmenu" target="_self"> {{ rendered_value }}
{% else %}
<a href="#drillmenu" target="_self"> {{ rendered_value }} *
{% endif %}
;;
drill_fields: [game]
}
Now Looker does not group by maturity_flag but all the values have * as if the condition is not fulfilled at all.
What am I missing here?