Conditional formatting using value_format

It's common for users to want to interact with short numbers. For example, they prefer $2.5M to a long string like $2,523,093.25. You can build short-number displays by creating multiple defined LookML measures, such as one to display as-is, one divided by 1,000, or one with limited decimal places, and so on.

LookML can achieve these displays automatically with some more advanced value_format syntax.

Syntax

Use this pattern to set value formats with conditions:

 [if_condition]format; [if_condition]format; else_format

Note that Looker only supports up to two if_condition conditions. If you add more than two conditions, any conditions after the first two will be ignored.

Since the value of the value_format parameter is already enclosed in double quotes in LookML, any nested double quotes will need to be escaped using the backslash \ character.

Example with large numbers

To make numbers such as 12.23M or 2.33K, you can use the following code:

 [>=1000000]0.00,,\"M\";[>=1000]0.00,\"K\";

Or with no decimal places:

 [>=1000000]0,,\"M\";[>=1000]0,\"K\";0

Here is the measure in LookML using this format, with dollar signs added:

 measure: global_amount { type: sum sql: ${TABLE}.total value_format: "[>=1000000]$0.00,,\"M\";[>=1000]$0.00,\"K\";$0.00" drill_fields: invoices* }

Example with large negative numbers

You can use similar syntax with negative numbers. To make numbers such as -12.23M or -2.33K, you can use the following code:

 [<=-1000000]0.00,,\"M\";[<=-1000]0.00,\"K\";

Or with no decimal places:

 [<=-1000000]0,,\"M\";[<=-1000]0,\"K\";0

Here is the measure in LookML using this format, with dollar signs added:

 measure: global_amount_negative { type: sum sql: ${TABLE}.total value_format: "[<=-1000000]$0.00,,\"M\";[<=-1000]$0.00,\"K\";$0.00" drill_fields: invoices* }