Conditional formatting of color or images within table cells using html

lindsey1 · January 20, 2015, 11:45pm

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:

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:

Learn more about using liquid html in Looker here.

anonymous · March 13, 2015, 11:16am

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

lindsey1 · March 13, 2015, 5:07pm

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).

eFein · June 24, 2015, 3:27pm

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

anonymous · March 14, 2016, 10:10am

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.

anonymous · April 16, 2016, 2:42pm

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?

mikhailxu · April 16, 2016, 11:46pm

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!

mslovich · August 23, 2016, 10:27pm

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.

anonymous · August 27, 2016, 12:17am

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

anonymous · September 9, 2016, 9:52pm

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.

anonymous · September 28, 2016, 8:31pm

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.

anonymous · September 28, 2016, 10:01pm

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.

anonymous · October 24, 2016, 11:19pm

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

maxcorbin · October 25, 2016, 12:48am

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!

anonymous · October 25, 2016, 4:04am

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?

maxcorbin · October 27, 2016, 6:26pm

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

anonymous · January 20, 2017, 9:16pm

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!

rufus · January 20, 2017, 11:11pm

@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.

anonymous · January 23, 2017, 3:47pm

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!

anonymous · January 23, 2017, 4:04pm

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?

Topic		Replies	Views
How to Change Colors and Fonts Using HTML Technical Tips & Tricks	0	226	June 22, 2022
Using HTML and Liquid with Looker Looker	1	69	October 15, 2015
Using HTML and Liquid to create 'progress' bars inside a Table Vis Looker lookml	0	90	November 16, 2017

Conditional formatting of color or images within table cells using html

AI Suggested topics