Conditional formatting of color or images within table cells using html

  • 21 January 2015
  • 28 replies
  • 14921 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

Userlevel 5
Badge


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.

Userlevel 1

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?

Reply