Format Numbers

You can customize the format of the numbers for the metrics you add to your visualizations. 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 Formats

The following table shows the predefined number formats:

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.

   
   

   The Custom Format menu opens.

3. Edit the definition:

   • Insert your own, custom definition.
   • Select a template from the Templates menu.
   
   

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:

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:

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.

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

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).

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.

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:

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:

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.

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 metrics 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.

  Note

  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: 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:

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:

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.