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 setting | Default 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.3 |
#.0 | Rounds to the nearest tenths place; for whole number values, zero shown in tenths place | 19676916585.269 | 19676916585.3 |
#.## | 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.521 | 1 days, 09:28:43.521 hours |
34123.521 | 0 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.75 | 23:45 hours |
38.5 | 1 day, 14:30 hours |
64 | 2 days, 16:00 hours |
Also in this section: