Qlik Number Formatting
- How-Tos FAQs
- November 4, 2020
Qlik Number Formatting
When we think of number formatting, we want as much flexibility as possible to meet any reasonable client request. Fortunately, Qlik allows for such flexibility, if you know where to look.
Every field in Qlik has a default number format. Number Formats are edited in the Format Pattern.
Occasionally the default number format is not relevant throughout the document, or one may wish to format some fields optionally with or without thousand separators for example.
The default number format can be set in the Document or Chart Properties in Qlikview, separators appear when selecting “Fixed to” settings:
Take note of the Format Pattern.
The Format Pattern is how Qlik interprets the format to display in the front end.
Edit this pattern directly within the Properties Dialog.
(*Note also that you can alter the character used for Decimal and Thousand Separators and how it displays in the front end) Removing a separator from the format pattern ###0 instead of #,##0 will display no thousand formats.
Several Format Patterns are recognized and used in Qlik; selecting between these Settings Types will display different default values for each. These defaults will differ according to Document Properties or initial Load Statements containing Format Set Statements:
SET TimeFormat=’h:mm:ss TT’;
SET TimestampFormat=’MM/DD/YYYY h:mm:ss[.fff] TT’;
Different Settings Types:
|Settings Type||Description||Sample Format Pattern
These can be edited manually
|Mixed||Both numbers and text. Numbers are shown in the original format.||n/a|
|Number||A number shows numeric values with the number of digits set in the Precision spinner box.||###########,###
|Integer||Shows numeric values as integers.||#,##0
|Fixed to||Shows numeric values as decimal values with the number of decimal digits set in the Decimals spinner box.||#,##0
|Money||Shows values in the format set in the Format Pattern edit box. An example of this format is in the Preview text box. The default format is the Windows Currency setting.||$#,##0.00;($#,##0.00)
|Date||The format set in the Format Pattern edit box show values interpreted as dates. An example of this format is in the Preview text box.||MM/DD/YYYY
|Time||The format set in the Format Pattern edit box shows values interpreted as time. An example of this format is in the Preview text box.||h:mm:ss TT|
|Timestamp||The format set in the Format Pattern edit box shows values interpreted as date + time. An example of this format is in the Preview text box.||MM/DD/YYYY h:mm:ss[.fff] TT|
|Interval||The format set in the Format Pattern edit box shows values interpreted as date + time. An example of this format is in the Preview text box.||h:mm:ss TT|
|Show in Percent (%)||Number, Integer, and Fixed formats are affected by this check box.|
Where are the Document charts and Sheet Properties in Qlik Sense?
Document and Chart properties are Qlikview specific.
On the Qlik Sense side, we have Sheet and Field Properties:
In the Top Right of the Sheet, use the “Edit Sheet” button (Available to Data Manager users):c
Once clicked, Sheet Properties opens up:
Additional Properties are opened in the top left corner in Qlik Sense as well.
Click on “Fields” to expand:
Select a Field to modify by clicking on it and then clicking the button to Create Measure:
Enable Number Formatting – To Edit the Format Pattern Select the “Number” type of formatting:
Now one can toggle between default (Simple) formatting or manually editing the Format Pattern:
Use a Format Pattern with the text box:
Conclusion of Qlik Number Formatting:
Understanding how the Format Pattern works opens up a huge variety and the flexibility to make custom changes to how formats are presented.
Making changes in these text boxes gives us an immediate view of what the output will look like, this is also very useful when doing Number Formatting in Expressions. It is as easy as a Copy and Paste of these Qlik Number Formatting Patterns, into the syntax for number functions.
Up Next: Learn How to Start Loving the Qlik Synthetic Key