User Guide Getting Started Help Center Documentation Community Training
New LookML
Old LookML
New LookML
  
English
日本語
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 Looker cells using Excel-style formats. In most cases, you may prefer to use value_format_name, reserving value_format for handling unique situations.

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)" }

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.

Examples

Some of the most common formatting options are shown here (please note 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" }

Top