Conditional formatting using value_format

The content of this article has been updated and migrated to a Looker Help Center article.

But, the information in the comments is useful, so the article will remain here for discussion. For the actual contents, please visit the Help Center.

4 25 10.2K
25 REPLIES 25

Rick_Saporta
Participant II

Is it possible to add more than 2 conditions, either via nesting or otherwise?

Rick,

As of the current build of Looker, we have no recommendations for more than three options. I’ll happily share this with our product team to see if its possible to expand this.

Rui_Zhang1
Participant I

Can we use another dimension to fill [if_condition]?
E.g. if we have dimension value and type

  • measure: total
    type: sum
    sql: ${TABLE}.value
    value_format: [type=‘count’]0;[type=‘amount’]$0.00;0.00

Rui_Zhang1
Participant I

I think I found a way to do it using HTML based on this post, but it would be better if we can do it using value_format.

  • measure: total
    type: sum
    sql: ${value}
    html: |
    {% if type._value == ‘currency’ %}
    ${{ value }}
    {% else %}
    {{ rendered_value }}
    {% endif %}

I think this value formatting is really cool. I have been playing with this value formatting for a while as we wanted to prefix Hours/Minutes/Seconds along with our ETL execution time in our dashboards and it works like a gem.

Value_format Used: [>=0]#" Minutes"

Thought i would share this to the group. One thing we felt that would be really cool is to apply styles.colors based on a certain threshold limit, especially to a single value visualization.

brayden
Participant III

Seconding Rick’s request for more than two conditions. I’ve hit the two condition limit on quite a few occasions now. In my most recent use case, I found there’s no way to format both positive and negative values fancily depending on their magnitude. e.g.

p_talkad
Participant III

Is this a work around for text values? I have a comments field that I have pulled over and would love for it to wrap so that it is not one long sentence, any suggestions for work arounds?

Is this functionality still supported in version 4.14.7?

Hi @Danielle_Dalton,

Yep, you can still do this! In New LookML, value_format is enclosed in double quotes, which means you need to escape the double quotes in the format, like so:

value_format: "[>=1000000]$0.00,,\"M\";[>=1000]$0.00,\"K\";$0.00"

I’ll update the original article to reflect this; thanks for pointing it out!

kmahamk
Participant I

Is there any way to conditionally specify the “Value format” based on the dimension? For example if i will need to display the counts based on a specific record type as Whole number(0) and decimal (0.00) based on another dimension.

How can i refer to the "Value format " option in HTML tag to conditionally do this based on dimension value is my question. Appreciate your help.

@kmahamk

Do you mind expanding on your question a little bit? Are you wanting to display a single field with two different formats depending on a certain condition?

kmahamk
Participant I

Yes. Correct. I want to format a single measure conditionally based on a dimension. For example the record type is A display the measure value as integer else display the measure as a decimal value.

Just a quick note if you want to do this with “special characters” you need to escape them. E.g., if I want this conditional formatting for the British Pound, £, I would escape it by writing \"£\" so the entire value format would look like:
value_format: "[>=1000000]\"£\"0.00,,\"M\";[>=1000]\"£\"0.00,\"K\";\"£\"0.00"

@kmahamk

Sorry for the LONG lag in response, but perhaps a workaround that will work in your case is discussed in this comment:

The workaround being creating identical (identical except for name) measures with the different formatting you desire. Then using a liquid if statement in the html: parameter of your measure to specify which measure/formatting you want to output based on your conditions.

Adding this in here in case anyone is still dealing with this in 2019 and has nuanced requirements like me (I want the raw data to be in raw format, but the charted data to be in the condensed format). This code worked for me and is inputted in value format on the chart rather than in the LookML:

[>=1000000]$0.0,,"M";[>=1000]$0.0,"K";$0.00

moebe
Participant V

Hello together
the solution shown works very well with positive values.
However, if I represent a profit and loss figure, I unfortunately also have negative values.
How can I change the formatting to define also negative values like M (million), K (tousand) etc.?

Many thanks and greetings
Benjamin

ajones
Participant I

Try the options explained here.

The negative values will appear in parenthesis.

moebe
Participant V

In the meantime I have found a solution for most format problems.
I can now display all numbers and visualizations and tables correctly.

I have achieved this by following liquid html formatting:

in the project manifest i added

constant: value_format_liquid_currency {
value: “{% if {{value}} >=1000000 %}
{{ value | divided_by: 1000000 | round: 2 | times: 1 }} M
{% elsif {{value}} <=-1000000 %}
-{{ value | divided_by: 1000000 | round: 2 | times: -1 }} M
{% elsif {{value}} <=-1000 %}
-{{ value | divided_by: 1000 | round: 2 | times: -1 }} K
{% elsif {{value}} >=1000 %}
{{ value | divided_by: 1000 | round: 2 | times: 1 }} K
{% elsif {{value}} <0 %}
-{{ value | divided_by: 1 | round: 2 | times: -1 }}
{% elsif {{value}} >0 %}
{{ value | divided_by: 1 | round: 2 | times: 1 }}
{% elsif {{value}} >= 0 %}
{{ rendered_value }}
{% endif %}”
}
constant: value_format_liquid_unit {
value: “{% if {{value}} >=1000000 %}
{{ value | divided_by: 1000000 | round: 2 | times: 1 }} TWh
{% elsif {{value}} <=-1000000 %}
-{{ value | divided_by: 1000000 | round: 2 | times: -1 }} TWh
{% elsif {{value}} <=-1000 %}
-{{ value | divided_by: 1000 | round: 2 | times: -1 }} GWh
{% elsif {{value}} >=1000 %}
{{ value | divided_by: 1000 | round: 2 | times: 1 }} GWh
{% elsif {{value}} <0 %}
-{{ value | divided_by: 1 | round: 2 | times: -1 }} MWh
{% elsif {{value}} >0 %}
{{ value | divided_by: 1 | round: 2 | times: 1 }} MWh
{% elsif {{value}} >= 0 %}
{{ rendered_value }}
{% endif %}”
}

Now all positive and negative values are displayed correctly in both visualizations and tables. I don’t juse the value_format for formating of values anymore…

Then I created the following named_value_format:

named_value_format: large_number_currency {value_format:"[>=1000000]"“0.00,” M";[<=-1000000]""-0.00," M";[>=1000]"“0.00,” K";"“0.00”}
named_value_format: large_number_unit {value_format:"[>=1000]"“0.00,” GWh";[<=-1000]""-0.00," GWh";"“0.00” MWh""}

Now there is one last problem. The value format of looker allows 3 layers. So it is possible to display the “hack” with M for values >1000000, K for values >1000 and the “rest” normally, but only if everything is positive. With negative values this doesn’t work anymore.

This means that any visualization with an axis cannot be scaled correctly.
I solved this with the workaround, that I set M correctly and thus, strangely enough, K is also correct.

I don’t think this can be solved for now. You have liquid html influence on the values, but not on the scaling label of a visualization.

Hi @Morgan1, this is very helpful! Is it possible to add a condition that uses “or” or “and” in the value_format?

@Morgan1 and @jakeyoos the solutions you have provided are potentially quite dangerous as it flips the sign on negative numbers which may not be obvious to end users, i.e.

value_format: "[>=1000000]$0.00,,\"M\";[>=1000]$0.00,\"K\";$0.00"

This sends:

  • 1500000 → $1.50M 
  • 1500 → $1.50K 
  • 15.789 → $15.79 
  • -15.78 → $15.79 
  • -1500 → $1500 
  • -1500000 → $1500000 

These last three are pretty terrible for revenue calculations where an organisation may think a business unit is profitable when in fact it’s loss making!

I’ve tried the following in Excel and it behaves as expected, so this feels like a bug on Looker’s end?

[>=1000000]£0.0,,"M";[>=1000]£0.0,"K";£0.00

It would be great if you could link to your conditional formatting help article within your documentation pages for named_value_format and value_format, or provide examples of conditional formatting in your example list, as this functionality is extremely useful (ignoring the issue with negative numbers!) and may not be common knowledge.

Finally, here’s our own workaround (this time using GBP), it’s not ideal but at least the numbers aren’t factually misleading which is our priority over how it looks:

value_format: "[>=1000]\"£\"0.0,\"K\";[<0]\"-£\"0.00;\"£\"0.00"

This sends:

  • 1500000 → £1500.0K ?‍♂️
  • 1500 → £1.5K 
  • 15.789 → £15.79 
  • -15.78 → -£15.79 
  • -1500 → -£1500.00 ?‍♂️
  • -1500000 → -£1500000.00 ?‍♂️

If there’s a better solution here, please let me know! Ideally, it would be good for Looker to behave the same as Excel in this regard, i.e. we shouldn’t have to “use up a condition” to handle negative values.

Hi @johnsandall - thanks for alerting us to this issue.  This behavior of flipping negatives is recent and has been filed as a bug.  I completely share your concern that it could be dangerous to data consumers.

One general workaround to the “3 conditions” limitation of the value_format parameter is to use the html parameter and use liquid variables to define the conditions.  This can get a bit lengthy since we are explicating everything, so I also might recommend using a constant to define the pattern.

Constants live in a manifest file, so if you don’t already have one, you can create one and write something like:

constant: big_pounds {
value: "<a href=\"#drillmenu\" target=\"_self\">
{% if value > 1000000000 %}
£{{ value | divided_by: 1000000000 | round:2 }}B
{% elsif value >= 1000000 and value < 1000000000 %}
£{{ value | divided_by: 1000000 | round:2 }}M
{% elsif value >= 1000 and value < 1000000 %}
£{{ value | divided_by: 1000 | round:2 }}K
{% elsif value >= 0 and value < 1000 %}
£{{ value | round:2 }}
{% elsif value > -1000 and value < 0 %}
- £{{ value | round:2 | times: -1 }}
{% elsif value > -1000000 and value <= -1000 %}
- £{{ value | divided_by: -1000 | round:2 }}k
{% elsif value > -1000000000 and value <= -1000000 %}
- £{{ value | divided_by: -1000000 | round:2 }}M
{% elsif value <= -1000000000 %}
- £{{ value | divided_by: -1000000000 | round:2 }}B
{% else %}
'fail'
{% endif %}
</a>"
}

The <a href> tag will preserve any drills that you defined.  

Then, in any measure that you want to use this format you can write:
 

measure: total_foo {
type: sum
sql: ${foo} ;;
html: @big_pounds ;;
drill_fields: [drillset*]
}

Let us know how it goes!

Just found a tiny typo in the example above when referencing a constant in lookml:

measure: total_foo {

type: sum

sql: ${foo} ;;

html: @{big_pounds}  ;;

drill_fields: [drillset*] }

And currently the formatting is not being passed through in the total value on table vis (new), but works fine in table vis (legacy), as well as data pane. I’ll file a bug for it.  

JohnRomanski
Participant III
constant: formatted_usd {
value: "<a href=\"#drillmenu\" target=\"_self\">
{% if value >= 100000000 %}
${{ value | divided_by: 1000000.00 | round:0 }}M
{% elsif value >= 1000000 and value < 100000000 %}
${{ value | divided_by: 1000000.00 | round:1 }}M
{% elsif value >= 1000000 and value < 10000000 %}
${{ value | divided_by: 1000000.00 | round:2 }}M
{% elsif value >= 1000 and value < 1000000 %}
${{ value | divided_by: 1000.00 | round:1 }}K
{% elsif value >= 0 and value < 1000 %}
${{ value | round:2 }}
{% elsif value > -1000.0 and value < 0 %}
- ${{ value | round:2 | times: -1 }}
{% elsif value > -1000000 and value <= -1000 %}
- ${{ value | divided_by: -1000.00 | round:1 }}K
{% elsif value > -10000000 and value <= -1000000 %}
- ${{ value | divided_by: -1000000.00 | round:2 }}M
{% elsif value > -100000000 and value <= -10000000 %}
- ${{ value | divided_by: -1000000.00 | round:1 }}M
{% elsif value <= -100000000 %}
- ${{ value | divided_by: -1000000.00 | round:0 }}M
{% else %}
'fail'
{% endif %}
</a>"
}

Here is a dollar formatting string  that I came up with for HTML. The goal here was to include at least three numbers of accuracy (‘$1.09K’ or ‘$10.9K’ or ‘$109K’ ).

Sadly this doesn’t appear in the chart view, and as mentioned, using conditional value_formats breaks with negative numbers.

elinvangbo
Participant I

Thank you @JohnRomanski for this solution!

It’s working the way expected but for some reason my cards that use Profit/Loss now have a blue font instead of the gray one and I can’t change it for some reason. I can’t see anything in your html changing the color. Do you know what the reason could be? 

/Elin

And currently the formatting is not being passed through in the total value on table vis (new), but works fine in table vis (legacy), as well as data pane. I’ll file a bug for it.  

@emma_wang have you had any update on this ?

It seems that the problem still exist (for Legacy tables as well, on my side) 

Thanks! 

Top Labels in this Space
Top Solution Authors