HTML reference to a dummy dimension

vsil
Participant I

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?

0 0 348
0 REPLIES 0
Top Labels in this Space
Top Solution Authors