Format Numbers

You can customize the format of the numbers for the metrics you add to your insights. For example, you can round numbers, shorten large numbers, display fixed number of zeros, display currency symbols, add colors, and so on.

For every metric and attribute, you can configure your own custom format using specific syntax or select from a variety of predefined formats.

Select Predefined Number Formats in the Metric Editor

Predefined formats for numbers are available when using the Metric Editor.

Follow these steps:

  1. Select the dropdown next to Number format.
  2. Select a predefined format.

    predefined number format

Predefined Number Formats

The following table shows the predefined number formats:

NameDescriptionFormatInput ExampleOutput Example
RoundedNumber rounded to the nearest whole number.#,##01,234.56781,235
Decimal (1)Number with one decimal digit.#,##0.01,234.56781,234.5
Decimal (2)Number with two decimal digits.#,##0.001,234.56781,234.56
Percent (rounded)Number multiplied by 100 rounded to the nearest whole number.#,##0%1,234.56781,235%
Percent (1)Number multiplied by 100 with one decimal digit.#,##0.0%1,234.56781,234.5%
Percent (2)Number multiplied by 100 with two decimal digits.#,##0.00%1,234.56781,234.56%

Create a Custom Number Format in the Metric Editor

A custom number format enables to you do the following for your metric:

  • Select a specific format from various formatting templates
  • Define your own number format
  • Adjust an already selected format.

Follow these steps:

  1. Select the dropdown next to Number format.
  2. Select Custom.

    custom number format


    The Custom Format menu opens.
  3. Edit the definition:
    • Insert your own, custom definition.
    • Select a template from the Templates menu.

      custom number format

  4. Select Apply.
    The format is applied to the metric.

Digit Placeholders

All number formats, whether predefined or custom, use the placeholders described in the following table:

PlaceholderDescription
0 (zero)Digit placeholder. The number of 0 (zeros) in the format definition corresponds with the number of digits displayed.
# (hash)Digit placeholder. Displays digits without extra 0 (zeros).
. (dot)Decimal point.
, (comma)Thousands separator.
% (percentage)Percentage placeholder. Multiplies the value by 100 and adds the % (percentage) character.

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.

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:

ExampleSyntaxInput ValueDisplayed Output
Preserving zeros in the ones place.#.##
0.##
0.05
0.05
.05
0.05
Preserving zeros in the case of a value of 0.#.##
0.##
0
0
null value (nothing displayed)
0
Preserving zeroes in the decimal places.#,#.##
#,#.00
1,000.00
1,000.00
1,000
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.

FormattingInput ValueDisplayed Value
#.##77
#.0077.00

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

SyntaxDescriptionInput ValueDisplayed Output
#Rounds to the nearest whole number.19676916585.26919676916585
#.0Rounds to the nearest tenths place; whole number values show zero in tenths place.19676916585.269
19676916585
19676916585.3
19676916585.0
#.##Rounds to the nearest hundredths place.19676916585.26919676916585.27
#.###Rounds to the nearest thousandths place.19676916585.26919676916585.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).

SyntaxDescriptionInput ValueDisplayed Output
#Rounds to the nearest whole number.1967691658519676916585
#,#Rounds to the nearest whole number; inserts commas every three place values.1967691658519,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.

SyntaxDescriptionInput ValueDisplayed Output
#,Rounds to the nearest thousand; removes ones, tens, hundreds digits.19676916585.26919676917
#,#,Rounds to the nearest thousand; removes ones, tens, hundreds digits; inserts commas every three place values.19676916585.26919,676,917
#,,Rounds to the nearest million; removes all digits up to a hundred thousands place.19676916585.26919677
#,,,Rounds to the nearest billion; removes all digits up to a hundred millions place;19676916585.26920
#,,,.##Divides number by one billion; rounds to the nearest hundredths place.19676916585.26919.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:

SyntaxDescriptionInput ValueDisplayed Output
#,KRounds to the nearest thousand; removes ones, tens, hundreds digits; appends the letter K after number.19676916585.26919676916K
#,,MRounds to the nearest million; removes all digits up to a hundred thousands place; appends the letter M after number.19676916585.26919677M
#,,,BRounds to the nearest billion; removes all digits up to a hundred millions place; appends the letter B after number.19676916585.26920B

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:

SyntaxDescriptionInput ValueDisplayed Output
#%Multiplies value by 100; rounds value to the nearest whole number; appends a percent symbol after number..5647256%
#.##%Multiplies value by 100; displays number’s first two decimal place values; appends a percent symbol after number..5647256.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%

Text Font Colors

Text color formatting is applicable to tables and headline reports only.

You can display report metric values in one of the following colors by inserting the color, between brackets [ ], at the start of the custom number syntax. For example:

[Blue]#,#.##

Available color codes:

  • Black
  • Blue
  • Cyan
  • Green
  • Magenta
  • Red
  • Yellow
  • White

You can also use hexadecimal color codes with the following syntax:

[color=99AE00]#,#.##

Cell Background Color

You can use hexadecimals to change the background color of cells with the following syntax:

[backgroundcolor=00FF00]#,#.##

To combine background color and font color syntax:

[color=99AAEE][backgroundcolor=000000]#,#.##
OR
[red][backgroundcolor=000000]#,#.##

Conditional Number Formatting

Use conditional formatting to define a number’s color or number format, contingent upon its value. Conditions define the range of number values to which a certain format should be applied.

Conditions are formed using brackets and their numerical ranges are defined using the symbols for the following:

  • greater than (>)
  • greater than or equal to (>=)
  • less than (<)
  • less than or equal to (<=)
  • equal to (=)

Conditional rules can be inserted anywhere formatting syntax, but they may be most helpful to place before formatting rules. The following example can be read as: “For number values less than or equal to 400,000, format in the following way…”

[<=400000][backgroundcolor=CCCCCC][red]$#,#.##

Separating Conditional Rules with Semicolons

You can set multiple formatting rules that define formatting for different value ranges by using semicolons ( ; ) to separate rules. Consider the following example:

[<600000][red]$#,#.##;[=600000][yellow]$#,#.##;[>600000][green]$#,#.##

This syntax breaks down into the following rules. For all values less than 600,000: values are displayed in red font:

[<600000][red]$#,#.##;

For all values equal to 600,000: values are displayed in yellow font:

[=600000][yellow]$#,#.##;

For all values greater than 600,000: values are displayed in green font:

[>600000][green]$#,#.##

Overlapping Conditions

Consider the following example rules (line-breaks added) where conditional formatting is used to create a temperature scale effect.

[<400000][red]$#,#.##;
[<500000][magenta]$#,#.##;
[<600000][yellow]$#,#.##;
[>=600000][green]$#,#.##

These rules are interpreted in the following sequence.

  1. All values less than 400,000 are red.
  2. All values greater than or equal to 400,000 and less than 500,000 are magenta.
  3. All values greater than or equal to 500,000 and less than 600,000 are yellow.
  4. All values greater than or equal to 600,000 are green.

Using Conditionals with Negative Numbers

The following example number format (#,#) applies to all values less than 100. In this case, the value –70,000 is displayed as 70,000.

[<100]#,#

The syntax could be modified to explicitly address this issue. In this example, all values less than zero are formatted in red and preceded by a negative symbol.

[<0][red]-#,#

You might also decide to use additional symbols, like parentheses, to denote negative data values:

[<0][red](#,#)

Using Conditionals with Null Values

By default, cells with NULL values are left blank. You can also apply conditional formatting to cells with NULL values with the [=NULL] tag. In the following example, cells of null values are displayed with gray background color and “No Value” written in red font.

[=Null][backgroundcolor=DDDDDD][red]No Value;

Example: Automatically Round Numbers

You can use conditional formatting to automatically round and truncate numbers according to their value.

The following syntax applies different formatting rules to values greater than or equal to one billion, between one million and one billion, and between one thousand and one million, as well to negative values of each of these ranges (line breaks added). Note the order in which the formatting rules are specified.

[>=1000000000]#,,,.0 B; 
[>=1000000]#,,.0 M; 
[>=1000]#,.0 K; 
[>=0]#,##0 
[<=-1000000000]-#,,,.0 B; 
[<=-1000000]-#,,.0 M; 
[<=-1000]-#,.0 K; 
[<0]-#,##0

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.

SyntaxDescriptionInput ValueDisplayed Output
$#Prepends a dollar sign before number19676916585$19676916585
##Prepends a symbol before number19676916585#19676916585
# gramsAppends a string of characters after number1967691658519676916585 grams
#,,,.# billionRounds to the nearest tenths of a billion; appends a string of characters after new number1967691658519.7 billion

For example, to display value in US dollars, you can use the following syntax 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 div calculation.
  • format
    The displayed output of the format applied to the input number divided by div modulo mod.

Example: Display fact input values of seconds in days, hours, minutes, and seconds

{{{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: Display fact input value of hours in days, hours, and minutes

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

Gives the following results:

Fact Input Value (in hours)Output Display
23.751 days, 09:28:43.521 hours
38.50 days, 09:28:43.521 hours
642 days, 16:00 hours

Format Metric Numbers through the API

To edit the format of metric numbers through the API, modify the contents of the format attribute with the desired placeholders. For more information, see Manage Metrics with the Entity API Interface .