Data Formatting Reference

If you're looking to format data in Vizlib KPI Designer, there are some rules you'll need to follow. In this article we've listed the rules and patterns used for formatting, and also displayed the results in table format to show you how the different applications of the rules help.

This topic contains the following sections:

Terminology

You can find a list of formatting terms in Table 1.

Term Description

Pattern Expression / Format Pattern

Input is provided in the Format Pattern field. Can be single i.e 00.00#$ or double i.e. (#.##pkt);-#.00. If input is single pattern and the number is negative, "-" will be added before the number. Adding "+" or "-" changes the output, see Condition 6 below.

Decimal Separator

Input is provided in the Decimal separator field. Used in numbers for separating integer and decimal parts.

Thousand Separator

Input is provided in the Thousand separator field. Used in numbers for separating individual thousand parts.

Application Decimal Separator

Set as an application variable in the data load editor, and used as the default decimal separator value.

Application Thousand Separator

Set as an application variable in the data load editor. It will be used as the thousand separator default value.

Table 1: Formatting Terminology

Separators - Special Characters

The characters in Table 2 can all be used in a pattern expression.

Character Description

#

Optional placeholder for number; if there is no number, in place of “#”, nothing will be added.

;

Used to separate formats when using double format.

%

If used, the number will be treated as percentage, so multiplied by 100.

+

Placeholder, if number is positive it will stay as “+”, if negative it will change to “-” .

-

Placeholder, if number is positive it will change to “+”, if negative it will stay as “-”.

_

Changed to space in a formatted number.

Table 2: Special Characters

Separators - Conditions

  1. For a pattern to be valid it must contain at least “#” or “0”.

  2. Decimal and Thousand Separators should match the separators used inside the format pattern, or custom number formatting may not work as expected.

  3. Anything added before or after the main part of the pattern (a combination of special characters) will be present in formatted number as prefix and suffix.

  4. In a double format pattern, the first pattern is always responsible for positive numbers, and the second pattern for negative numbers.

  5. Decimal and Thousand Separators in the property panel are for presentation purposes only.

  6. Positive numbers won't usually display "+", but "+" or "-" will be displayed if they are added in pattern. You can also customize the sign location e.g. to display before a currency sign you can use +$#,##0.00.

Separators - Text and Threshold Fields

Vizlib KPI Designer has some rules to follow when you're entering data into text or threshold fields.

  1. The user can only type values formatted with a comma, dot and decimal separator if they are defined in the app's configuration. Underscores and other special characters are not currently supported.

  2. The thousand separator is not currently supported in Text and Threshold fields, as visualization numbers and number definition can't be mixed.

We've included some use cases in Table 3 to demonstrate some supported and unsupported formats.

Use Case Supported/Not Supported

After formatting - 100.000.123 (Dot as Decimal Separator).

Not supported

After formatting - 100.000.123 (Comma as Thousand Separator).

Supported

Text Field Input - 100000.123 - Number

Supported

Text Field Input - 100,000.123- String

Not Supported

Table 3: Text and Threshold Fields Use Cases

Separators - Limitations

  1. In the pattern field, a decimal separator should go after a thousand separator. It won’t work the other way. (e.g. for decimal separator set as “.” and thousand separator set as “,” pattern #.##0,0 is not valid, but #,##0.0 is valid).

  2. Input for a text field value can’t be a string if the Format as number option is enabled. (e.g 123.456 is correct but 123,456 or 100,000.123 are not).

Possible Variations - Table Views

Qlik App Configuration 1: Decimal separator: ‘.’ Thousand separator: ‘,’

Format

Positive

Negative -10,000

Negative (10,000)

Positive Currency

Negative Currency -10,000

Negative Currency (10,000)

10,000

123,456,789

-123,456,789

(123,456,789)

$123,456,789

-$123,456,789

($123,456,789)

10,000.0

123,456,789.1

-123,456,789.1

(123,456,789.1)

$123,456,789.1

-$123,456,789.1

($123,456,789.1)

10,000.00

123,456,789.12

-123,456,789.12

(123,456,789.12)

$123,456,789.12

-$123,456,789.12

($123,456,789.12)

10k

123M

-123M

(123M)

$123M

-$123M

($123M)

10.0k

123.5M

-123.5M

(123.5M)

$123.5M

-$123.5M

($123.5M)

10.00k

123.46M

-123.46M

(123.46M)

$123.46M

-$123.46M

($123.46M)

100%

12,345,678,912%

-12,345,678,912%

(12,345,678,912%)

-

-

-

100.0%

12,345,678,912.3%

-12,345,678,912.3%

(12,345,678,912.3%)

-

-

-

100.00%

12,345,678,912.34%

-12,345,678,912.34%

(12,345,678,912.34%)

-

-

-

auto

123M

-123M

(123M)

$123M

-$123M

($123M)

Table 4: Qlik App Configuration 1

Qlik app Configuration 2: Decimal separator: ‘*’ Thousand separator: ‘&’

Format

Positive

Negative -10,000

Negative (10,000)

Positive Currency

Negative Currency -10,000

Negative Currency (10,000)

10&000

123&456&789

-123&456&789

(123&456&789)

$123&456&789

-$123&456&789

($123&456&789)

10&000*0

123&456&789*1

-123&456&789*1

(123&456&789*1)

$123&456&789*1

-$123&456&789*1

($123&456&789*1)

10&000*00

123&456&789*12

-123&456&789*12

(123&456&789*12)

$123&456&789*12

-$123&456&789*12

($123&456&789*12)

10k

123M

-123M

(123M)

$123M

-$123M

($123M)

10*0k

123.5M

-123.5M

(123.5M)

$123.5M

-$123.5M

($123.5M)

10*00k

123.46M

-123.46M

(123.46M)

$123.46M

-$123.46M

($123.46M)

100%

12&345&678&912%

-12&345&678&912%

(12&345&678&912%)

-

-

-

100*0%

12&345&678&912*3%

-12&345&678&912*3%

(12&345&678&912*3%)

-

-

-

100*00%

12&345&678&912*34%

-12&345&678&912*34%

(12&345&678&912*34%)

-

-

-

auto

123M

-123M

(123M)

$123M

-$123M

($123M)

Table 5: Qlik App Configuration 2

Custom Format

0.11

-0.11

0,11

0&11

123456,789

123

-123

#,##0.00

0.11

-0.11

0.11

0.11

123,456.79

123.00

-123.00

#.##0,00

0,11

-0,11

0,11

0,11

123.456,79

123,00

-123,00

$#,##0.00

$0.11

$-0.11

$0.11

$0.11

$123,456.79

$123.00

$-123.00

+$#,##0.00

+$0.11

-$0.11

+$0.11

+$0.11

+$123,456.79

+$123.00

+$123.00

#,##0.00$

0.11$

-0.11$

0.11$

0.11$

123,456.79$

123.00$

-123.00$

(#,##0.00)

(0.11)

(-0.11)

(0.11)

(0.11)

(123,456.79)

(123.00)

(-123.00)

#,##0.00%

11.00%

-11.00%

11,00%

11&00%

12345679,90%

12300.00%

-12300.00%

#_##0.00

0.11

-0.11

0.11

0.11

123 456.79

123.00

-123.00

+#,##0.00

+0.11

-0.11

+0.11

+0.11

+123,456.79

+123.00

-123.00

-#,##0.00

+0.11

-0.11

+0.11

+0.11

+123,456.79

+123.00

-123.00

00,0

00,1

-00,1

00,1

00,1

123456,8

123,0

-123,0

0,000

0,110

-0,110

0,110

0,110

123456,789

123,000

-123,000

#0,#

0,1

-0,1

0,1

0,1

123456,8

123

-123

0,0##

0,11

-0,11

0,11

0,11

123456,789

123,0

-123,0

(#,#);-#,00pkt

(0,1)

-0,11pkt

(0,1)

(0,1)

(123456,7)

(123)

-123,00pkt

Table 6: Custom Format

Number Formatting

Number  10K  10.0K 10.00K  10,000 10,000.0 10,000.00

0

0

0.0

0.00

0

0.0

0.00

0.1236

0

0.1

0.12

0

0.1

0.12

-0.1236

-0

-0.1

-0.12

-0

-0.1

-0.12

1.236

1

1.2

1.24

1

1.2

1.24

12.36

12

12.4

12.36

12

12.4

12.36

123.6

124

123.6

123.60

124

123.6

123.60

1236

1K

1.2K

1.24K

1,236

1,236.0

1,236.00

12360

12K

12.4K

12.36K

12,360

12,360.0

12,360.00

123600

124K

123.6K

123.60K

123,600

123,600.0

123,600.00

1236000

1M

1.2M

1.24M

1,236,000

1,236,000.0

1,236,000.00

12360000

12M

12.4M

12.36M

12,360,000

12,360,000.0

12,360,000.00

123600000

124M

123.6M

123.60M

123,600,000

123,600,000.0

123,600,000.00

Table 7: Number Formatting Examples