Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Liquid HTML With Number Format

  • 22 September 2016
  • 10 replies
  • 4082 views
J
Userlevel 1

I know I can adjust the number format for a given measure as a whole. But I want to have the same measure have different number formats depending on what is in a given column. Is there a way to have multiple number formats for the same measure? Basically something like below, which isn’t working


   html: |

         {% if metric_name._value contains 'Orders'%}

            <div style="text-align:center;font-weight: bold; number-format='#,##0' ">{{ rendered_value }}</div>

         {% else %}

            <div style="text-align:center;font-weight: bold; number-format='0.00\%'">{{ rendered_value }}</div>

         {% endif %}

10 replies

Brett_Sauve
Rank
Userlevel 3

@josmeye check out this post, which I think has what you need:



J
Userlevel 1

Tried implementing that but it didn’t specify if you could use a dimension as the if condition; someone asked but never saw an answer. Below is what I have tried, but it doesn’t seem to work


   value_format: '[metric_name._value == "Orders %"]#,##0;metric_name._value == "Units Ordered %"]$0.00;0.00\%'
J
Userlevel 1

Bumping back to the top for visibility

L
Rank
Userlevel 5
Badge

@josmeye - value_format does not accept Liquid HTML, since Excel’s formatting is independent of Liquid and HTML. Similarly, HTML cannot use value_format. In your case, I would recommend making separate hidden fields for each value_format you want. Then you can pick the appropriate field via HTML. This would look something like:



Old LookML
- dimension: number_format

  type: number

  hidden: yes

  value_format: '#,##0'

  sql: ${TABLE}.column_name



- dimension: your_dimension

  type: number

  value_format: '0.00\%'

  sql: ${TABLE}.column_name

  html: |

    {% if metric_name._value contains "Orders" %}

      {{ number_format._rendered_value }}

    {% else %}

      {{ rendered_value }}

    {% endif %}



New LookML
dimension: number_format {

  type: number

  hidden: yes

  value_format: "#,##0"

  sql: ${TABLE}.column_name ;;

}



dimension: your_dimension {

  type: number

  value_format: "0.00\%"

  sql: ${TABLE}.column_name ;;

  html: {% if metric_name._value contains "Orders" %}

      {{ number_format._rendered_value }}

    {% else %}

      {{ rendered_value }}

    {% endif %}

    ;;

}

J
Userlevel 1

This is for a measure though not a dimension

L
Rank
Userlevel 5
Badge

@josmeye It should still work the same way. Just duplicate the definition of your current measure, change the value_format, make them hidden, and then reference those measures in the HTML of the non-hidden measure. You could actually streamline this a bit by only duplicating the measure only once instead of twice (I edited the above dimension example as well to reflect this). Something like this:



Old LookML
- measure: number_format

  type: sum  # or whatever the type of your current measure is

  hidden: true

  value_format: '#,##0'

  sql: ${TABLE}.column_name



- measure: your_dimension

  type: sum  # or whatever the type of your current measure is

  value_format: '0.00\%'

  sql: ${TABLE}.column_name

  html: |

    {% if metric_name._value contains "Orders" %}

      {{ number_format._rendered_value }}

    {% else %}

      {{ rendered_value }}

    {% endif %}



New LookML
  measure: number_format {

  # or whatever the type of your current measure is

  type: sum

  hidden: yes

  value_format: "#,##0"

  sql: ${TABLE}.column_name ;;

}



measure: your_dimension {

  # or whatever the type of your current measure is

  type: sum

  value_format: "0.00\%"

  sql: ${TABLE}.column_name ;;

  html: {% if metric_name._value contains "Orders" %}

      {{ number_format._rendered_value }}

    {% else %}

      {{ rendered_value }}

    {% endif %}

    ;;

}

J
Userlevel 1

Ok I was able to get it working. For anyone else that needs this, I had to add in the required_fields: [metric_percentage] variable at the end. Even with hiding the visualization it wasn’t working, but after adding in the required_fields variable it started showing up as expected

tig
Rank

For each old LookML code block in this article and its comments, we just added the New LookML equivalent code.

E

I struggled with implementing this, so I figured I would share. In my case, in the example above the metric_name._value was pivoted in my explore. As a result, it’s value was blank or null for some reason. In order to get this to work, I had to create a measure off of metric_name that was using the same sql field. I pivoted the original metric_name in my explore, but evaluated the new metric_name_measure in the liquid syntax to get to the conditional format I was after.

cjr

Hi, I’m trying to implement the above solution, I feel like I’m pretty close but something is missing.


  dimension: number_format {

    type: number

    hidden: yes

    value_format: "$0.00"

    sql: ${TABLE}.metric_level ;;

  }



  dimension: metric_level {

    type: number

    value_format: "0"

    sql: ${TABLE}.metric_level ;;

    html: {% if metric_name._value contains "urgent" %}

      {{ number_format._rendered_value }}

      {% else %}

      {{ rendered_value }}

      {% endif %}

      ;;

  }

For all the metrics that don’t contain “urgent” they are being formatted as integers, so that bit is working! However for the one row I have where the metric_name contains “urgent” it is just coming up as an empty cell. Any ideas why this is? I thought it was to do with the hidden flag but changing that didn’t fix it.


EDIT: Didn’t read josmye’s most recent comment. Using required_field: [number_format] at the end solved it!

Reply


Terms of Service