Liquid HTML With Number Format

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 %}

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

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\%'

Bumping back to the top for visibility

@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 %}
    ;;
}
1 Like

This is for a measure though not a dimension

@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 %}
    ;;
}

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

2 Likes

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

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.

1 Like

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!

1 Like