I’ve got some large numbers in my result sets. I would much rather display these as shortened numbers, like 2.5M, rather than a large number like 2,509,626. Additionally, if my number is in the billions, I’d like it to display as 3.9B rather than 3911.2M.
We can use value format strings to change how numbers are formatted.
For each comma, the number is divided by one thousand, so two commas divides the result by one million.
The billions rule will just add another comma:
Now, to apply the billions rule only when the number is over a billion, we’ll use conditional value formats. The general form is:
[if_condition]format; [if_condition]format; else_format
In our example, this will look like:
[>=1000000000]0.0,,," B";[>=1000000]0.0,," M";0
Using zero as the final format means that the number will be shown in full if it’s less than a million.
Applying Value Formats in LookML
If we define a value_format on the field in LookML, then the value format will be applied in all visualizations that use that field. Because the value format is inside a double quoted string, we’ll want to use
\ to escape the quotes in our string, like this:
value_format: "[>=1000000000]0.0,,,\" B\";[>=1000000]0.0,,\" M\";0"