Value format in looker. We are trying to break number in billiion, million and thousands dynamically

[akhilsai]

Folks,

We are trying to format numbers in Looker dynamically in billions, million, and thousands dynamically. Below we tried in millions and thousands. It working as expected. But we are not able to figure out negative numbers. Appreciate it if we get any help.

named_value_format: millions {
value_format: "[>=1000000]0.0,,\" M\";[>=1000]0.000,\" K\";0"
}

[Saty]

To Google Looker Folks
Need Value format for negative numbers.
I was trying this :  value_format: "[>=1000000]0.0,,\"M\";[>=1000]0.0,\"K\";[>=0]0;[<=-1000000]-0.0,,\"M\";[<=-1000]-0.0,\"K\";-0" but it was not working. Any Insights on this?  Appreciate it if we get any help.

[Mohammad_Ali]

In manifest you can define the formatting and then you can apply in the dimension/measure.

[Saty]

constant: negative_format {
  value: "{% if value < 0 %}<p style='color:red;'>({{rendered_value}})</p>{% else %} {{rendered_value}} {% endif %}"
}

[Saty]

html:
{% if value >= 1000000 and value < 1000000000 %}
${{value | divided_by: 1000000 | round:1 }}M
{% elsif value >= 1000 and value < 1000000 %}
${{value | divided_by: 1000 | round:1 }}K
{% elsif value >= 0 and value < 1000 %}
${{value | round:0 }}
{% elsif value > -1000 and value < 0 %}
${{value | round:0 }}
{% elsif value > -1000000 and value <= -1000 %}
${{value | divided_by: 1000 | round:1 }}k
{% elsif value > -1000000000 and value <= -1000000 %}
${{value | divided_by: 1000000 | round:1 }}M
{% else %}
'fail'
{% endif %} ;;
}