Usage

Definition

The value_format parameter lets you format data values in Looker using Excel-style formats. You can use value_format in the following ways:

• On its own, at the field level, to apply Excel-style formatting directly to a specific dimension or measure
• As a subparameter of the named_value_format parameter, at the model level, to create a reusable custom format that can be applied to multiple fields

Applying value_format directly to dimensions and measures

The value_format parameter can be used on both dimensions and measures. To apply Excel-style formatting to dimensions via value_format, the dimension type must be number. To apply Excel-style formatting to measures via value_format, make sure the measure is numeric and not of type: string. For example:

Using value_format to define a reusable custom format

For information about using value_format with named_value_format to define reusable formats, see the named_value_format parameter’s documentation page.

Default formatting options

If you prefer to apply one of Looker’s built-in value formats, you can choose from the formats listed on the Default Format Names section of the value_format_name parameter’s documentation page. See that page for instructions on using value_format_name to apply built-in formats.

Common formatting strings

value_format accepts an Excel-style formatting string.

The formatting used with the value_format parameter is the same as formatting used in the Value Format field in visualizations, except that the value_format parameter requires the formatting string to be enclosed in double quotes. See the Column chart options documentation page for information about value formats in visualizations.

You can read Excel’s complete guide about how to specify these formats in their documentation. However, at this time, date formatting, color formatting, and hexadecimal conversion are not supported in Looker.

Some of the most common formatting options are shown here. Note that some special characters, such as international currency symbols, must be enclosed in double quotes.

Common challenges

Losing decimals when dividing

One common SQL quirk that arises when using value_format is the way that SQL handles integer math. If you divide 5 by 2, most people expect the result to be 2.5. However, many SQL dialects will return the result as just 2, because when it divides two integers it also gives the result as an integer. To address this, you can multiply the numerator by a decimal number (like 1.0 or 100.0) to force SQL into returning a decimal result. For example:

Using value_format with the number_format user attribute

If you use value_format to format fields in your models, the number format selected in the number_format user attribute is applied on top of the format applied with value_format. For an example, and to learn more, visit the Localizing number formatting documentation page.