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

b941d5b7-64c4-4863-b4e7-ab03b42988d4.png
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.

Comments
LukasM
Observer

@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 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.

3601b4ef-e188-4983-b3db-47e69861f354.png

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;

caf064aa-698e-41b2-80ce-3782a2a4ed8d.png

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
Observer

Thanks @Pieter_DeVries ,

I should have mentioned that I know this already 😄

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?

Version history
Last update:
‎05-25-2021 07:41 PM
Updated by: