Vizlib Home Try Vizlib
Welcome
Login

Vizlib KPI Designer Guides - 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. 


TABLE OF CONTENTS


Terminology


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


Table 1: Formatting Terminology

TermDescription
Pattern Expression / Format PatternInput 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 SeparatorInput 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. 


Back to Contents


Separators - Special Characters 


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


Table 2: Special Characters

CharacterDescription
0Obligatory placeholder for number; if there is no number, in place of “0”, “0” will be added.
#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.


Back to Contents


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.


Back to Contents


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. 


Table 3: Text and Threshold Fields Use Cases

Use CaseSupported / 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 - NumberSupported 
Text Field Input - 100,000.123- StringNot Supported 


Back to Contents


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


Back to Contents


Possible Variations - Table Views


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

Table 4: Qlik App Configuration 1

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)


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

Table 5: Qlik App Configuration 2

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)


Custom Format

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


Back to Contents



 

K
Kenneth is the author of this solution article.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.