Localizing number formatting

Looker's default number format setting for numbers that appear in data tables and visualizations is 1,234.56. However, the number format can be set to any of the following:

• 1,234.56: Thousands separated with commas; decimals separated with a period
• 1.234,56: Thousands separated with periods; decimals separated with a comma
• 1 234,56: Thousands separated with spaces; decimals separated with a comma

Setting number formats for users

You can set a number format through one of the following methods:

• To set a number format for individual users:Select the desired format from the Number formatdrop-down menu on the Edit Userpage in the Adminpanel and click Saveat the bottom of the page.
• To set a number format for a user group:Assign the desired format to the number_format user attribute for a particular user group . If users within the group have set a custom value for number_format , the custom value will override any value that you assign to the group. To prevent the number_format that you assign from being overriden, ensure that the User Accesssetting for the number_format user attribute is not set to Edit.
• To set a number format for an entire instance:Assign one of the codes in the previous table to the Number formatfield on the Localizationpage of the Adminpanel.

The default number format in Looker — 1,234.56— displays numbers in thousands separated with commas, and decimals separated with a period.

For example, suppose you have an Explore with a column chart and data table that displays values for Orders Order Count, Order Items Average Sale Price, and Order Items Total Sales Pricegrouped by Orders Created Month. Values with the default setting will take the format x,xxx.xx.

If you change the Number formatsetting to 1.234,56, the values in visualizations and data tables will take the format x.xxx,xx:

The Number format setting with other methods of number formatting

If you use the LookML parameters value_format_name or value_format to format fields in your models, the number format selected in the Number formatsetting or number_format user attribute is applied on top of the format given in the LookML parameters. For example, suppose you have a measure that represents the count of pies with a specified value_format defined in LookML:

  measure 
 : 
  
 count 
  
 { 
  
 type 
 : 
  
 count 
  
 value_format 
 : 
  
 "####.0\"pies\"" 
  
 drill_fields 
 : 
  
 [ 
 detail 
 *] 
 } 
 

With the Number formatsetting set to 1.234,56, and the LookML value_format: "####.0\"pies\"" , visualizations and data tables display values in the format xxxx,xx pies. The Number formatsetting swaps a comma for the period for the value_format: "####.0\"pies\"" . A Countof 9,849 pies will display as 9849,0 pies.

Similarly, if you use the Value Formatfield in a visualization's Editmenu, the number format that is set in the Number formatsetting or the number_format user attribute will be applied on top of the format that is specified in the visualization Value Formatfield.

For example, if a user inputs the format ###0.000 in the Value Formatfield for a column chart, and the Number formatis set to 1.234,56, the visualization will display values in the format xxxx,xxxx. The Number formatsetting swaps a comma for the period for the visualization Value Formatsetting ###0.000 . A Countof 8,474 orders in the visualization will appear as 8474,000.

Overriding the Number format setting with strict_value_format

Typically, the number formatting set in the Number formatsetting or number_format user attribute is applied on top of formats applied by LookML parameters.

However, if you want to create a number format that is not affected by the Number formatsetting or the number_format user attribute, you can use the named_value_format model parameter to create a number format and set its strict_number_format subparameter to yes . You can apply that format to fields by using the value_format_name parameter, and those fields will not be affected by the Number formatsetting or the number_format user attribute.

For example, suppose that a custom format called dollar_formatting is defined in a model file. The custom format has a strict_value_format subparameter that is set to yes :

 named_value_format: dollar_formatting {
  value_format: "$#,###.00"
  strict_value_format: yes
} 

The order_items view file in the project has two measures, average_sale_price and average_spend_per_user . The dollar_formatting custom format is applied to the average_sale_price measure, but not to the average_spend_per_user :

 measure: average_sale_price {
  type: average
  value_format_name: dollar_formatting
  sql: ${sale_price} ;;
}

measure: average_spend_per_user {
    type: average
    sql: ${user_order_total_price} ;;
} 

The number formatting in the Number formatsetting is set to 1.234,56.

In data tables and visualizations, the values for the Order Items Average Sale Pricemeasure will appear in the format $x,xxx.xxand will not be affected by the Number formatsetting. For example, an Average Sale Priceof 45.63 will appear as $45.63in visualizations and data tables.

In data tables and visualizations, the values for the Order Items Average Spend per Usermeasure will be affected by the Number formatsetting and will appear in the format $x.xxx,xx. For example, an Average Spend per Userof 47.64 will appear as $47,64in visualizations and data tables.