Qlik Number Formatting

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.

Format Pattern

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.

Number format

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 ThousandSep=’,’;
SET DecimalSep=’.’;
SET MoneyThousandSep=’,’;
SET MoneyDecimalSep=’.’;
SET MoneyFormat=’$#,##0.00;($#,##0.00)’;
SET TimeFormat=’h:mm:ss TT’;
SET DateFormat=’MM/DD/YYYY’;
SET TimestampFormat=’MM/DD/YYYY h:mm:ss[.fff] TT’;
SET MonthNames=’Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec’;
SET DayNames=’Mon;Tue;Wed;Thu;Fri;Sat;Sun’;

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 #,##0.00
Fixed to	Shows numeric values as decimal values with the number of decimal digits set in the Decimals spinner box.	#,##0 #,##0.00 #,##0.000
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) € #,##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 YYYY-MM-DD
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