The Problem

I have 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.

The Solution

We can use value format strings to change how numbers are formatted.

These can be entered in both the Y Axis Format and Value Format sections of the visualization. Those docs have several examples of value format; in this case we’ll use the millions format: 0.0,," M"

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: 0.0,,," B"

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"