Formatting Numbers in Reports

Use the Number format editor to configure how numbers appear in your reports. You can round numbers, accommodate regional settings, add currency symbols, adjust colors, or append numbers with sets of characters.

Contents:

Regional Settings and Number Formatting

Formatting syntax depends on the Regional Number Formatting setting of your account. For more information, see Set Default Number Format for your User Account.

Formatting syntax examples in this article are based on the GoodData default regional setting:

  • Commas (,) are used to separate thousands.
  • Periods (.) are used to separate decimal places.

For example:

1,234.12

If you change your project's regional formatting settings (for example, the one that uses commas to separate decimal values and spaces to separate thousands), formatting syntax will change accordingly for all unmodified existing and all new metrics. Custom metric formatting syntax will be unaffected.

Example:

Account settingDefault syntax
1,234.56

#,##0.00

1 234,56# ##0,00

Accessing Number Format Editor

To access the Number format editor, click Edit in the Detail column of a metric.

Then apply formatting syntax in the custom number formats field.

Preserve Extraneous Zeroes

To preserve extraneous zeros, use a 0 in place of the hash symbol (#) in the number formatting syntax.

When # symbols are used in number formatting syntax, any zeros not holding a place value are removed. However, using a 0 in place of the # symbol in formatting syntax preserves extraneous zeros.

The following is the default number formatting syntax: 

#,##0.00

Implications:

  • #,##0.00: If the ones place is empty, a zero is displayed in its place.
  • #,##0.00: If there are no tenths or hundredths places in a value, zeroes are displayed in those places.

With this number formatting, the following are the results: 

Example

Syntax

Input Value

Displayed Output

Preserving zeros in the ones place

#.##

0.05

.05


0.##

0.05

0.05

Preserving zeros in the case of a value of 0

#.##

0

null value (nothing displayed)


0.##

0

0

Preserving zeroes in the decimal places

#,#.##

1000.00

1,000


#,#.00

1000.00

1,000.00

Rounding to a Whole Number and Decimal Places

The number of hash symbols (#) to the right of the decimal point dictate the number of decimal place values to display. If the input number has more place values than specified by the formatting syntax, the final decimal place value is rounded. For example, #.# applied to 7.25 results in a display value of 7.3.

To specify that decimal place values should be filled with zeros when null, use zeros in place of hash symbols for those decimal places in the number formatting syntax. 

Formatting

Input Value

Displayed Output

#.##

7

7

#.00

7

7.00


Otherwise, zeros and hash symbols are interchangeable in number-formatting syntax. 

Syntax

Description

Input Value

Displayed Output

#

Rounds to the nearest whole number

19676916585.269

19676916585

#.#

Rounds to the nearest tenths place; for whole number values, no tenths place shown

19676916585.269
19676916585

19676916585.3
19676916585

#.0

Rounds to the nearest tenths place; for whole number values, zero shown in tenths place

19676916585.269
19676916585

19676916585.3
19676916585.0

#.##

Rounds to the nearest hundredths place

19676916585.269

19676916585.27

#.###

Rounds to the nearest thousandths place

19676916585.269

19676916585.269

Separate Thousands, Millions, and so on

Insert a comma between hash symbols in custom number formatting syntax to separate thousands in the sets of numbers (thousands, millions, billions, and so on). 

Syntax

Description

Input Value

Displayed Output

#

Rounds to the nearest whole number

19676916585

19676916585

#,#

Rounds to the nearest whole number; inserts commas every three place values

19676916585

19,676,916,585

Truncating Large Numbers

Every comma added to the immediate left of the decimal point effectively truncates the number by another three place values, starting with the ones, tens, and hundreds places. 

Syntax

Description

Input Value

Displayed Output

#,

Rounds to the nearest thousand; removes ones, tens, hundreds digits

19676916585.269

19676917

#,#,

Rounds to the nearest thousand; removes ones, tens, hundreds digits; inserts commas every three place values

19676916585.269

19,676,917

#,,

Rounds to the nearest million; removes all digits up to hundred thousands place

19676916585.269

19677

#,,,

Rounds to nearest billion; removes all digits up to hundred millions place; appends the letter B after number

19676916585.269

20

#,,,.##

Divides number by one billion; rounds to nearest hundredths place

19676916585.269

19.68


You can provide context for truncated values by adding letters like K, M, and B (thousands, millions, and billions) to the custom number formatting syntax. These letters have no impact on the number's value; the letter is just inserted in the output: 

Syntax

Description

Input Value

Displayed Output

#,K

Rounds to the nearest thousand; removes ones, tens, hundreds digits; appends the letter K after number

19676916585.269

19676916 K

#,, M

Rounds to the nearest million; removes all digits up to a hundred thousands place; appends the letter M after number

19676916585.269

19677 M

#,,, B

Rounds to the nearest billion; removes all digits up to a hundred millions place; appends the letter B after number

19676916585.269

20 B

Display Interpretable Symbols

You can display symbols in your custom number formatting that might be otherwise interpreted by the GoodData Portal. To force the display of a literal in custom number formatting, precede the character with a backslash (\).

Suppose you want to display percentage symbols in your formatting. However, the percent symbol (%) has special meaning in custom formatting; it indicates that a data value should be multiplied by 100. If you add this symbol to the formatting, the value 97 is displayed as 9700%, instead of 97%.

To display the percent symbol without affecting the data value, use the following in your formatting: \%

Some examples are listed below:

Syntax

Description

Input Value

Displayed Output

#%

Multiplies value by 100; rounds value to the nearest whole number; appends a percent symbol after number

.56472

56%

#.##%

Multiplies value by 100; displays number's first two decimal place values; appends a percent symbol after number

.56472

56.47%

#.##\%

Displays number's first two decimal place values; appends a percent symbol after number; number value is not impacted by percent sign

.56472

.56%

Insert UTF-8 Characters (Currency Symbols, SI and Imperial Units, and so on.)

All UTF-8 symbols, except for commas, periods, and percent symbols, that are added to custom number formatting syntax are displayed alongside number values. 

Commas, periods, and percent symbols can be interpreted by the application as syntax commands.

Syntax

Description

Input Value

Displayed Output

$#

Prepends a dollar sign before number

19676916585

$19676916585

#

Prepends a symbol before number

19676916585

19676916585

# grams

Appends a string of characters after number

19676916585

19676916585 grams

#,,,.# billion

Rounds to the nearest tenths of a billion; appends a string of characters after new number

19676916585

19.7 billion

For example, to display value in US dollars, simply add the dollar sign ($) to the default format available in the Number format editor.

$#,##0.00

This displays the amount as is typical for the United States, for example, $1,234,567.89:

  • thousands separated by commas
  • decimal point
  • two decimal places (including amounts such as $1.00)

Similarly, use £ to display amounts in British pounds. You can also combine signs. For example, to specify that the amount is in Canadian dollars, use the C$ prefix.

You can also use UTF-8 characters to create bar displays. In the following example, a bar display is defined for metric values between 0.0 and 1.0:

[>=.9][color=2190c0]██████████;
[>=.8][color=2190c0]█████████░;
[>=.7][color=2190c0]████████░░;
[>=.6][color=2190c0]███████░░░;
[>=.5][color=2190c0]██████░░░░;
[>=.4][color=2190c0]█████░░░░░;
[>=.3][color=2190c0]████░░░░░░;
[>=.2][color=2190c0]███░░░░░░░;
[>=.1][color=2190c0]██░░░░░░░░;
[color=2190c0]█░░░░░░░░░

Unit Conversion in Metric Formatting 

Unit conversion allows you to implement simple arithmetics to format measures into various display outputs such as duration or length.

For example, an input of seconds can show an output of HH:MM by converting seconds into hours and remaining minutes and applying the correct format on the result.

Format

Arithmetic format blocks can be included anywhere in the custom format string but they cannot be nested.

They have the following structure:

{{{div|mod|format}}}

These blocks receive the same input number as the main formatting string that they are part of.

Format blocks can be parameterized in the following way:

  • div - the argument that divides the input number.
  • mod - the modulo calculated from the result of the the div calculation.

    If mod does not end with dot (.), the decimal part of the number is stripped (therefore not rounded). Otherwise, the decimal part is preserved.

  • format - the displayed output of the format applied to the input number divided by div modulo mod.

Example 1

{{{86400||#}}} days\, {{{3600|24|00}}}:{{{60|60|00}}}:{{{|60.|00.000}}} hours

Gives the following results

Fact Input Value (in seconds)Output Display
120523.5211 days, 09:28:43.521 hours
34123.5210 days, 09:28:43.521 hours

Example 2

{{{24||[>1]# days\, ;[>0]# day\, ;#}}}{{{|24|0}}}:{{{0.016666666|60.|00}}} hours

Gives the following results

Fact Input Value (in hours)Output Display
23.7523:45 hours
38.51 day, 14:30 hours
642 days, 16:00 hours


Also in this section: