Custom data formats in views

You can change the way that data is formatted in a view by creating a custom format, if the data format that you want isn't available in the built-in formats.

Creating a custom format can be useful when working with views. For example, you might want to specify a particular currency symbol, such as the Euro (€), or use a different date or time format.

Important: When you set the format in the view, the underlying TM1 format or TM1 cell data type is not changed. You can reset the format in the view to the underlying TM1 format by selecting Use database format.

Custom formatting in IBM® Planning Analytics Workspace views uses ICU syntax. For detailed information, go to the following website: https://unicode-org.github.io/icu/userguide/strings/unicodeset.html.

Custom formats use a specific pattern, and many characters in a pattern are taken literally, so they appear unchanged. The following example shows how you can insert the word Total into rows or columns, and it also shows how to insert a currency symbol (€) into the value.

Example custom format	Result
Total: €#,##0;(€#,###)	Value: `28.50` Displays: `Total: €29`

Notice that the # and 0 characters are used to represent digits. The # and 0 characters are examples of special pattern characters. The following table provides a summary of the special characters that are used in custom formats.

Table 1. Special pattern characters
Symbol	Meaning
`0`	Digit Can be used in combination with #. For example, `#,##0.00;(#,##0.00)` Displays insignificant zeros if a number has fewer digits than there are zeros in the format string. If a number has more digits to the right of the decimal point than there are placeholders in the format string, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. Examples: Custom format: 0.00 Value: 23.896 Displays: 23.90
`#`	Digit, zero shows as absent Can be used in combination with 0. # displays only significant digits. In the value .90, the 0 is considered insignificant. The value is displayed as .9 when # placeholder is used. If a number has more digits to the right of the decimal point than there are placeholders in the format string, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. Example: Custom format: #.## Value: 123.896 Displays: 123.9
`1-9`	'1' through '9' indicate rounding
`@`	Significant digit Example: Custom format: @@@ Value: 123.896 Displays: 124
`.`	Decimal separator or monetary decimal separator
`-`	Minus sign
`,`	Grouping separator Example: Custom format: `#,##,##0` Value: 123456789 Displays`:12,34,56,789` You can also use trailing commas at the end of a numeric format pattern to display values in compact format, For more information, see Custom compact numeric format.
`E`	Separates the mantissa and exponent in scientific notation
`+`	Prefix positive exponents with the localized plus sign
`;`	Separates positive and negative sub-patterns If you don't specify how negative numbers are shown, positive formatting is used, prefixed by the minus sign. Example: Custom format: #,##0.00;(#,##0.00) Positive numbers: `123,456,789.00` Negative numbers: `(123,456,789.00)`
`%`	Multiply by 100 and show as percentage
`'`	Use single quotation marks to include special characters in a prefix or suffix Example: Custom format: `"'$'#"` Value: 123 Displays: `$123`. To display a single quotation mark itself, use two single quotation marks in a row. For example, for the value 7, the pattern `# o''clock` displays `7 o'clock`.

Custom compact numeric format

You can append one or more commas to the end of a custom number format pattern to display values in compact format in explorations and visualizations. Each comma present at the end of a format pattern scales the value display by 1,000.

A single comma results in the value being displayed as thousands. Two commas display the value as millions. Three commas display the value as billions.

For example, the format pattern #,##0.0, results in the value 1234567.89 being displayed as 1,234.6. The format pattern #,##0.0,, displays the same value as 1.2.

To improve the usability of compact format, you can include literals in the format pattern to display specified characters. You can use these characters to indicate that the value is displayed as thousands, millions, or billions. Literals must be enclosed in single quotation marks.

To improve upon the previous example, use the format pattern #,##0.0,'K' to display the value 1234567.89 as 1,234.6K. The format pattern #,##0.0,,'M' displays the same value as 1.2M.

Date and time examples

For custom date formats, you use a date pattern. In a date pattern, strings of characters are replaced with date and time data.

Example	Result
hh:mm a	12:00 AM 06:00 PM
EEE, MMM d, ''yy	Mon, Oct 30, '15

For more information, go the https://unicode-org.github.io/icu/userguide/format_parse/datetime website, and look for Date/Time Format Syntax.