Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

More than three conditional/dynamic value formats. INCLUDING negative numbers

Pieter_DeVries
Rank
Userlevel 2
Those sweet sweet Bs Ms and Ks

 

Huge shoutout goes to @Hugo_Selbie who came up with this formula.

    dimension: value {
      sql: CAST(${TABLE}.value as integer) ;;
      type: number
      html: 
          {% 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 }}
          {% 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 %} ;;
    }

We use the html here, because standard “big_money” formatting with excel type conditions has a limit of 3 conditions.

named_value_format: big_money {
  value_format:"[>=1000000000]0.0,,,\"B\";[>=1000000]0.0,,\"M\";[>=1000]0.0,\"K\";0.0"
}

 

Using Liquid allows us to create more flexible formatting and also incorporate negative numbers easily.

3 replies

LukasM

@Pieter_DeVries On a surface level this seems to work but showing value labels in, for example, a line chart still does not use the proper formats.

 

Is there a way to get that done as well? I get the impression that right now Looker just can’t do proper number formatting all to the end. :/ 

Pieter_DeVries
Rank
Userlevel 2

@Pieter_DeVries On a surface level this seems to work but showing value labels in, for example, a line chart still does not use the proper formats.

 

Is there a way to get that done as well? I get the impression that right now Looker just can’t do proper number formatting all to the end. :/ 

Hi @LukasM, unfortunately you are correct for the value label, as shown below.

In the case of using value labels, we need to edit the value format for the visualization, and it is closer to the lookml value format, so like this;

The logic here being,
[>1000000]$#,,"M";$#,"K"
If larger than 1million, then truncate 2 commas worth of data and add a M to the end, if not then truncate 1 comma worth of data and add a K.
Because this is basically the same as value_format the maximum amount of logic is 2 ifs and an else.

LukasM

Thanks @Pieter_DeVries ,

 

I should have mentioned that I know this already :D

 

Unfortunately my number can both be large and positive and large and negative. I’m looking at something like a profit here. From how I understand it, the alternative solution does not cover this scenario. Do you also have a solution for this case?

Reply


Terms of Service