Question

Conditional formatting of color or images within table cells using html

  • 21 January 2015
  • 28 replies
  • 8073 views

Userlevel 5
Badge

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.


28 replies

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

Userlevel 5
Badge

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

Userlevel 4

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


Userlevel 1

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?

Userlevel 3

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.

Userlevel 1

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

Userlevel 3

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.

Userlevel 3

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.

Userlevel 1

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 %}
Userlevel 3
Badge

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?

Userlevel 3
Badge

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

{% else %}
[some other action]
{% endif %}

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



{% elsif value > 30 and different_field.value == “Okay” %}

[color yellow]

{% else %}

[color green]

{% endif %} ;;

}


Let me know; thanks!

Userlevel 3

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

{% 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.

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?

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.

Userlevel 4
Badge

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

Userlevel 1

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]

}

Userlevel 3

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:


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

Reply