I’m trying to create a simple KPI dashboard with conditional formatting of a Single Value visualisation, using a comparison, where the formatting (e.g. the background colour) conditions are based on the percentage achievement rather than absolute values. Is this possible?
Solved! Go to Solution.
A couple things you can do instead.
dimension: target {
type: number
sql: 200000 ;;
}dimension: target1 {
type: number
sql: 1 ;;
}
measure: total_order_count {
type: count_distinct
sql: ${order_id} ;;
}
measure: pct_of_target_label_value {
type: number
sql: 1.0* ${total_order_count} / ${target} ;;##use html to display value from total order count instead of calculated percent
html: {{total_order_count._rendered_value}} ;;
}
You add a target1 and your calculated pct_of_target_label_value fields
Because the measure is actually a pct (but displayed as original integer value) you can add your comparison without a label. And use the value for conditional formatting.
Now if you want to include the percentage, the simplest way is to add it to your html label like below (you’d have to play a bit with formatting if you want on a separate line).
measure: pct_of_target_label_value_and_pct {
type: number
view_label: "zz Test Metrics"
sql: 1.0* ${total_order_count} / 200000 ;;
value_format_name: percent_0
html: <a style="font-size:24px;"><b>{{total_order_count._rendered_value}}</b></a>
<a style="font-size:8px;">({{rendered_value}} of target)</a>;;
}
measure: count_pct_of_target {
type: number
view_label: "zz Test Metrics"
sql: 1.0* ${total_order_count} / ${target} ;;
#sql: .50 ;;
value_format_name: percent_0#if value over 50% then green else red
html: {% if value >= 0.50 %}
{% assign bg_color = "0,180,0" %}
{% else %}
{% assign bg_color = "255,0,0" %}
{% endif %}
<div style="float: left
; width:{{ value | times:100}}%
; background-color: rgba({{bg_color}},{{ value | times:100 }})
; text-align:left
; color: #FFFFFF
; border-radius: 5px"> <p style="margin-bottom: 0; margin-left: 4px;">{{rendered_value}}</p>
</div>
<div style="float: left
; width:{{ 1| minus:value | times:100}}%
; background-color: rgba({{bg_color}},0.1)
; text-align:right
; border-radius: 5px"> <p style="margin-bottom: 0; margin-left: 0px; color:rgba(0,0,0,0.0" )>{{rendered_value}}</p>
</div>
;;
}
When added to your viz it looks like this:
You can also edit to include the integer value too. e.g., where you have {{rendered_value}} above replace with {{total_order_count._rendered_value}} ({{rendered_value}})
In addition to the link DMcintosh provided, here are links to other great articles on html conditional formatting (I pulled the second example from the Progress Bars doc):
Conditional Format Table Cells
Advanced Conditional Formatting
Hopefully these gives you some more ideas!
Hi Simon, there doesn’t appear to be a way to do that. It would certainly be a great feature though that would help quickly identify the health of a KPI. (Looker product reps should take note of the value of this!)
Hi Simon,
Try using conditional formatting to change the Font Color (rather than background color). The color of bar will match the font color.
Thanks @JenniferThomas for taking the time to respond. Unfortunately your suggestion doesn’t quite do what I want. In your example I’d like the condition statements to be
- Green text if more than 50% of target achieved
- Red text if less than 50% of target achieved
Instead, as you show, I have to write
- Green text if more than 125,000
- Red text if less than 125,000
But my targets change every month, so it might be 300,000 next month, so my colours would be wrong! I’d like to set the conditions on the percentage not on the absolute values.
I found this on the subject the other day. It’s not exactly what you are looking for but its another option you could entertain.
I also saw a thread on this same subject - being able to apply conditional formatting based on another column value (like target) rather than hardcoded values. The post was from 4 years ago and some Looker reps commented in it saying they were looking at the functionality. But, that was 4 years ago and it seems like they haven’t addressed it.
A couple things you can do instead.
dimension: target {
type: number
sql: 200000 ;;
}dimension: target1 {
type: number
sql: 1 ;;
}
measure: total_order_count {
type: count_distinct
sql: ${order_id} ;;
}
measure: pct_of_target_label_value {
type: number
sql: 1.0* ${total_order_count} / ${target} ;;##use html to display value from total order count instead of calculated percent
html: {{total_order_count._rendered_value}} ;;
}
You add a target1 and your calculated pct_of_target_label_value fields
Because the measure is actually a pct (but displayed as original integer value) you can add your comparison without a label. And use the value for conditional formatting.
Now if you want to include the percentage, the simplest way is to add it to your html label like below (you’d have to play a bit with formatting if you want on a separate line).
measure: pct_of_target_label_value_and_pct {
type: number
view_label: "zz Test Metrics"
sql: 1.0* ${total_order_count} / 200000 ;;
value_format_name: percent_0
html: <a style="font-size:24px;"><b>{{total_order_count._rendered_value}}</b></a>
<a style="font-size:8px;">({{rendered_value}} of target)</a>;;
}
measure: count_pct_of_target {
type: number
view_label: "zz Test Metrics"
sql: 1.0* ${total_order_count} / ${target} ;;
#sql: .50 ;;
value_format_name: percent_0#if value over 50% then green else red
html: {% if value >= 0.50 %}
{% assign bg_color = "0,180,0" %}
{% else %}
{% assign bg_color = "255,0,0" %}
{% endif %}
<div style="float: left
; width:{{ value | times:100}}%
; background-color: rgba({{bg_color}},{{ value | times:100 }})
; text-align:left
; color: #FFFFFF
; border-radius: 5px"> <p style="margin-bottom: 0; margin-left: 4px;">{{rendered_value}}</p>
</div>
<div style="float: left
; width:{{ 1| minus:value | times:100}}%
; background-color: rgba({{bg_color}},0.1)
; text-align:right
; border-radius: 5px"> <p style="margin-bottom: 0; margin-left: 0px; color:rgba(0,0,0,0.0" )>{{rendered_value}}</p>
</div>
;;
}
When added to your viz it looks like this:
You can also edit to include the integer value too. e.g., where you have {{rendered_value}} above replace with {{total_order_count._rendered_value}} ({{rendered_value}})
In addition to the link DMcintosh provided, here are links to other great articles on html conditional formatting (I pulled the second example from the Progress Bars doc):
Conditional Format Table Cells
Advanced Conditional Formatting
Hopefully these gives you some more ideas!