value_format

LookML
Version

On this Page
Docs Menu

Go to Field Parameter List

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%" # 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'

Still have questions?
Go to Discourse - or - Email Support
Top