Looker Community

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.


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


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.

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

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.

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.

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!

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.

1 Like


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?

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.

1 Like

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"

1 Like


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:

1 Like

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

Try the options explained here.

The negative values will appear in parenthesis.

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.