
Huge shoutout goes to
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.