Conditional formatting of color or images within table cells using html

Imagine you have a field in your data called status, which gives the status of each order. The possible values for status are:

Paid
Shipped
Returned

While Exploring your data, you might want to have a separate background color for each status. This can be done using liquid html in the html: parameter of a dimension. This would look something like:

 dimension: status {
  sql: ${TABLE}.status ;;
  html: 
    {% if value == 'Paid' %}
      <p style="color: black; background-color: lightblue; font-size:100%; text-align:center">{{ rendered_value }}</p>
    {% elsif value == 'Shipped' %}
      <p style="color: black; background-color: lightgreen; font-size:100%; text-align:center">{{ rendered_value }}</p>
    {% else %}
      <p style="color: black; background-color: orange; font-size:100%; text-align:center">{{ rendered_value }}</p>
    {% endif %}
;;
}

In your table, this will look like:

d8a56dfe54fb2326.png

You can use the same syntax to add icons or images based on cell values:

  dimension: status { 
    sql: ${TABLE}.status ;;
    html: 
      {% if value == 'Shipped' or value == 'Complete' %}
         <p><img src="http://findicons.com/files/icons/573/must_have/48/check.png" height=20 width=20>{{ rendered_value }}</p>
      {% elsif value == 'Processing' %}
        <p><img src="http://findicons.com/files/icons/1681/siena/128/clock_blue.png" height=20 width=20>{{ rendered_value }}</p>
      {% else %}
        <p><img src="http://findicons.com/files/icons/719/crystal_clear_actions/64/cancel.png" height=20 width=20>{{ rendered_value }}</p>
      {% endif %}  
;;
}

In your table, this will look like:

f37b4f3942b09d21849cce23cc04cf07756b75d0.jpg

Learn more about using liquid html in Looker here.

14 28 23.1K
28 REPLIES 28

Alison1
Participant IV

Lindsay, what version of Looker did CSS start being supported in? I’ve tried to do formatting before and Looker stripped it out.

Hey Alison! Formatting of this type has been supported since we added liquid html formatting (prior to version 3.0). In this case, you’ll want to make sure you are using {{ rendered_value }}, rather than {{ linked_value }} or {{ value }}, in order to see the formatting. This should display the formatting in all relevant table cells (but not in visualizations).

If you would like tighter formatting in each formatted cell, replace the <p>,</p> tags with <div>,</div>

acd9f1d31883027de2db9f7c9e054454ae3fefb8.png

wdijkhuizen
Participant II

Thanks lindsey and eFein. Question: is there already a feature request to make conditional formatting available as part of the “Table” visualization? I.e.: I would like to have the possibility that the end user can define color encoding in a table visualization, instead of me defining it upfront in LookML.

Has there been any follow up to this? I’m new to the discourse, but how do we submit feature requests and follow their progress?

Hey @jscheller and @wdijkhuizen!

We have an open feature request for this and I have added you both to it!

At some point we will broaden table visualization options to end users who don’t know HTML or have access to the model. We know that customizing the table vis is an important feature and we want to build this correctly. We don’t have a solid near term plan for building this out yet, but it is something we’re aware about and want to improve.

To submit feature requests you can post a topic in Whats Next? Feature Requests category. You’ll also be added to our internal requests tool and we will update you on all progress!

Hi @jscheller and @wdijkhuizen!

As @mikhailxu mentioned, customization for table visualizations is an important feature that we want to make sure we build correctly. In the meantime, there are some existing patterns you can leverage to call attention specific rows based on conditions business users can define in table calculations in the Looker Explore UI.

Please check out this discourse post for more on this topic.

dion1
Participant I

Here is another pattern using CASE WHEN to build a nifty heatmap.

sql: | 
  CASE 
  WHEN (1.0 * ${field1} / nullif(${field2},0)) < 1 
  THEN 1.0 * ${field1} / nullif(${field2},0) 
  WHEN (1.0 * ${field1} / nullif(${field2},0)) >= 1 AND (1.0 * ${field1} / nullif(${field2},0)) <=5 
  THEN (1.0 * ${field1} / nullif(${field2},0))/5 
  WHEN (1.0 * ${field1} / nullif(${field2},0)) > 5 
  THEN 1 
  ELSE NULL 
  END 
value_format: '0.00' 
html: | 
  {% if balance_foo._value > 1 %} 
  <div style="background-color: rgba(200,35,25,{{value}}); font-size:150%; text-align:center"><a href="{{ link }}" style="color: white" target="_new">{{ balance_foo._rendered_value }} </a></div> 
  {% elsif balance_foo._value > 0 %} 
  <div style="background-color: rgba(25,35,150,{{value}}); font-size:150%; text-align:center"><a href="{{ link }}" style="color: white" target="_new">{{ balance_foo._rendered_value }} </a></div> 
  {% else %} 
  <div style="background-color: rgba(25,35,150,0.99); font-size:150%; text-align:center"><a href="{{ link }}"style="color: white" target="_new">{{ balance_foo._rendered_value }} </a></div> 
{% endif %}

ross2
Participant III

I would say that not only is this an important feature to make available to end users, but also that given how frequently we need to make use of table functions in order to accommodate pivot-wise calculations that are not supported in the model, limiting conditional formatting to the model view significantly reduces its value.

ross2
Participant III

Just ran into this issue again. I don’t see why a feature which is being applied to a SQL results set should be limited in any way whatsoever by the method in which said results set is generated.

zachtaylor
Participant II

Hey Ross - thanks for your feedback. We are planning to make conditional formatting available in the UI. It’s a feature we are hoping to address in the near/medium term.

How do you do an if/else statement based on whether the value exists or not? I’m trying to the following but it’s not rendering.

         {% if value >= 10 %}
        <div style="color: black; background-color: red; font-size:100%; text-align:center">{{ rendered_value }}</div>
      {% elsif value >= 5 %}
        <div style="color: black; background-color: #ff8080; font-size:100%; text-align:center">{{ rendered_value }}</div>
      {% elsif not value %}
        <div style="color: black; background-color: red; font-size:100%; text-align:center">{{ rendered_value }}</div>
      {% endif %}

Hey @MDiggity! You’re really close here. Nil (or NULL) values are actually treated as false in Liquid, so you can just leave out the “not” and this should render. Give it a try and let me know how it goes!

Hey @maxcorbin, not sure I follow 100%. Can you help with the statement based on the one I used above? I want to show red if the value is NULL. I’m interpreting what you’re saying as:

{% elsif value %}
      [make background red]
{% endif %}

Which would show background if value exists, not if value is NULL, right?

Ah, I think I understand what you mean. You may be able to use {% unless %} for this: http://shopify.github.io/liquid/tags/control-flow/#unless

{% unless value %}
  [color background red]
{% else %}
  [some other action]
{% endif %}

kdietz
Participant I

This is really helpful! Is there any way to pull additional looker fields from the same view into your if statement?

Example:
dimension: count {
sql: ${TABLE}.count
html:
{% if value <= 30 and different_field.value == “Good” %}
[color red]
{% elsif value > 30 and different_field.value == “Okay” %}
[color yellow]
{% else %}
[color green]
{% endif %} ;;
}

Let me know; thanks!

@kdietz You can reference other fields using the {{ field._value }} reference

Something like this should work for you:

dimension: count{
sql: ${TABLE}.count ;;
html:
   {% if {{ value }} <= 30 and {{ differentfield._value }} == "Good"%}
      [color red]
   {% elsif {{ value }} > 30 and {{ differentfield._value }} == "Okay" %}
      [color yellow]
   {% else %}
      [color green]
   {% endif %} ;;
}

We have a great doc on all the liquid variables you can use in an html parameter here.

kdietz
Participant I

Hi @rufus thanks so much for the response! Unfortunately referencing {{ differentfield._value }} doesn’t seem to work for me.

I should note I’m applying these if statements to a measure that is an average, and the differentfield._value is a dimension I am pivoting on in the Look that I am creating. Would this change anything in the way these if statements compute?

Thanks!

Is there any chance any time soon of getting real (i.e. built into LookML) conditional formatting rather than this 3rd party, hack workaround of using liquid-html?

kdietz
Participant I

As a follow up to the above, when I unpivot the differentfield that I am interested in formatting on, the formatting works. Looks like it’s the pivoting that is the issue.

@Groovy_Growler we’re hoping to release conditional formatting support for tables in Looker 4.8 - just under a month from now.

@kdietz - you’re hitting a bug that is distinct to pivots + liquid formatting, something that should be trumped by the release of conditional formatting support in 4.8 (mentioned above)

wdijkhuizen
Participant II

Although I’m not a regular user of Looker anymore, I would like to thank Zam and the Looker Product team for releasing it in 4.8 (see Looker 4.8 Release Notes)!

Is there a way to apply conditional formatting with liquid HTML and retain drilling capability? Trying to change a single value viz to show a specific color and retain drilling to additional detail.

dimension: revenue_health {
type: string
hidden: yes
html:
{% if value == ‘Red’ %}
{{linked_value}}
{% elsif value == ‘Yellow’ %}
{{linked_value}}
{% else %}
{{linked_value}}
{% endif %} ;;
sql: ${TABLE}.revenue_health__c ;;
drill_fields: [revenue_health_risk_reason_details]
}

Hey @mkirchhevel , there is! You’ll want to follow the format shown in this discourse, Preserving Drill Fields when Formatting a Measure. That format works for both, dimensions and measures.

For example, if I was conditionally formatting my state dimension and wanted to preserve drill fields, I would use:

  dimension: state {
    type: string
    sql: ${TABLE}.state ;;
    html: {% if value == 'California' %}
          <a href="{{ link }}" style="background-color: lightblue;">{{ value }}</a>
          {% endif %};;
    drill_fields: [name, age]
  }

And this is how it’d look on the explore page:8417624ad41835ea4c62e950350e1694344f6d10.png

Thanks so much! This worked great. Sorry I didn’t find it when searching discourse earlier!

Explaining Dion’s sweet heatmap example above. The key is deciding on a few colors, and then using the data value to set the opacity.

In this case, we’re going to show all values greater than 1 in some shade of red, and all values between 0 and 1 in some shade of blue.

We decide on one red value, rgb(200,35,25), and one blue value, rgb(25,35,150). Then instead of rgb we use rgba, which takes in a fourth value, opacity. We stick the number {{value}} in there so that the opacity changes with the data.

Here’s a simplified version of the above LookML, converted to new LookML.

measure: some_ratio {
type: number
sql:  1.0 * ${some_sum} / ${some_count} ;;
html: 
  {% if balance_foo._value > 1 %} 
  <div style="background-color: rgba(200,35,25,{{value}}); font-size:150%; text-align:center"><a href="{{ link }}" style="color: white" target="_new">{{ balance_foo._rendered_value }} </a></div> 
  {% elsif balance_foo._value > 0 %} 
  <div style="background-color: rgba(25,35,150,{{value}}); font-size:150%; text-align:center"><a href="{{ link }}" style="color: white" target="_new">{{ balance_foo._rendered_value }} </a></div> 
  {% else %} 
  <div style="background-color: rgba(25,35,150,0.99); font-size:150%; text-align:center"><a href="{{ link }}"style="color: white" target="_new">{{ balance_foo._rendered_value }} </a></div> 
{% endif %} ;;
}

Of course, it is possible to achieve a lot of this heatmap functionality with the visualization heatmaps, but this is a good exercise in liquid HTML.

It would be really good for certain users in our organisation to perform conditional formatting on the fly with html in their own custom fields. It is somewhat inflexible if Dev users need to create multiple fields set in LookML for different ‘versions’ of conditional formatting on a field applicable only to certain individuals or groups in the Org.

Beto
Participant II

Hello everyone !!

I’m new to looker and wanted to conditional format several tables. My issue is that I only have some measures defined at a model level. The rest of the measures are calculated using table calculations on the corresponding explores.

How can I add conditional formatting for those table calculations?

Top Labels in this Space
Top Solution Authors