User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
Looker
  
English
Français
Deutsch
日本語
value_format

Usage

view: view_name {
  dimension: field_name {
    value_format:"$#.00;($#.00)"
  }
}

Hierarchy

value_format

Possible Field Types

Dimension, Measure

Accepts

A string containing Excel-style formatting

Definition

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

Applying value_format Directly to Dimensions and Measures

The value_format parameter can be used on both dimensions and measures. For example:

dimension: order_amount { type: number sql: ${TABLE}.order_amount ;; value_format: "$#.00;($#.00)" } measure: total_order_amount { type: sum sql: ${order_amount} ;; value_format: "$#.00;($#.00)" }

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 this 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.

value_format: "0" # Integer (123) value_format: "*00#" # Integer zero-padded to 3 places (001) value_format: "0 \" String\"" # Integer followed by a string (123 String) # Please note \"String\" can be replaced with any other word   value_format: "0.##" # Number up to 2 decimals (1. or 1.2 or 1.23) value_format: "0.00" # Number with exactly 2 decimals (1.23) value_format: "*00#.00" # Number zero-padded to 3 places and exactly 2 decimals (001.23) value_format: "#,##0" # Number with comma between thousands (1,234) value_format: "#,##0.00" # Number with comma between thousands and 2 decimals (1,234.00) value_format: "0.000,,\" M\"" # Number in millions with 3 decimals (1.234 M) # Please note division by 1 million happens automatically value_format: "0.000,\" K\"" # Number in thousands with 3 decimals (1.234 K) # Please note division by 1 thousand happens automatically   value_format: "$0" # Dollars with 0 decimals ($123) value_format: "$0.00" # Dollars with 2 decimals ($123.00) value_format: "\"€\"0" # Euros with 0 decimals (€123) value_format: "$#,##0.00" # Dollars with comma btwn thousands and 2 decimals ($1,234.00) value_format: "$#.00;($#.00)" # Dollars with 2 decimals, positive values displayed # normally, negative values wrapped in parenthesis   value_format: "0\%" # Display as percent with 0 decimals (1 becomes 1%) value_format: "0.00\%" # Display as percent with 2 decimals (1 becomes 1.00%) value_format: "0%" # Convert to percent with 0 decimals (.01 becomes 1%) value_format: "0.00%" # Convert to percent with 2 decimals (.01 becomes 1.00%)

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:

measure: active_users_percent { type: number sql: 100.000 * ${active_users} / ${users} ;; value_format: "0.000" }

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 Looker documentation page.

Top