LinkedIn
Twitter
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
1 ACCEPTED SOLUTION
A couple things you can do instead.
- Create the % of Target Calculation as a Measure and use that in your single-value viz and use in the Conditional Formatting Option BUT display the integer value instead. For example:
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>;;
}
- A second option is to build your own progress bar. Here’s an example measure.
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!
