Formatting Functions

Formatting functions are used to format the way integers are displayed in visualisations. All formatting functions return a dual value, which means they will display in a ‘user friendly’ way and yet in the backend, Qlik will be able to read the functions as an integer for sorting purposes. These functions are essential for creating easy to digest, user friendly apps.

This article will cover the basic principles of working with formatting functions in the data load editor and visualisations, including:

The general function syntax
Working with formatting functions in the data load editor
Working with formatting functions in charts

For a complete, up-to-date list of formatting functions, please see the Qlik Sense help pages here.

The formatting function syntax

The syntax will differ depending on the function. For up-to-date syntax, please do refer to the Qlik help pages here.

Understanding the function syntax given by Qlik Sense

It isn’t always easy to decipher the syntax provided in the help pages linked to above if you aren’t used to reading it. In this section, we will explain how to read this syntax so that you can apply this thinking to every function in the help pages.

To explain the syntax, we will use an example function.

Example 1: Date

The Date() function formats a string in the specified date format. This is an incredibly useful function for displaying dates in a format other than the data source format.

Copy code


Date(number[, format])

The rules for reading this are as follows:

The function name itself is first and all the parameters you need to enter go between brackets: ().
The various elements you will need to enter are separated by commas. In this function, there are 2 elements: the number and [format].
Anything between square brackets [] is an optional parameter. In this function, the [format] parameter is optional. If omitted, Qlik will format the string as per the system variable date format. If you do include the parameter, DO NOT include brackets.

An example of this function being used with the optional parameter included could be:

Copy code


Date("Date",'DD-MMM-YY')

The above function will return the values in the “Date” field formatted as ‘DD-MMM-YY’, e.g. 01-Mar-23.

An example of the function without using optional parameters may be:

Copy code


Date("Date")

The values in the “Date” field will now be formatted as per the ‘DateFormat’ system variable.

How to use formatting functions in the data load editor

Now that you understand how to read the syntax of Qlik functions, you need to understand how to use these functions in the load. The rules are:

Some of the functions can be used throughout the whole load script, not just within a LOAD or SELECT statement
They are particularly useful for creating dual value dimensions
The functions can be nested and combined with other types of functions

A simple example of using the Dual() function to order a dimension specifically in visualisations is as follows:

Copy code


SITE_SESSIONS:
LOAD
	"URL",
    "Sessions",
    Dual("URL",Pick(Match("URL", 'www.my-site-3.com', 'www.my-site-1.com', 'www.my-site-2.com'),1,2,3)) as "URL Order Importance"
    
FROM [my-data.qvd] (qvd);

The resulting table will have 3 fields: “URL”, “Sessions” and “URL Order of Importance”. The fields “URL” and “URL Order of Importance”, visually, will look identical. However, if you were to plot a bar chart using the field “URL” as a dimension and sort the data by “URL”, the values will be sorted alphabetically, i.e. ‘my-site-1’, ‘my-site-2’, ‘my-site-3’. If the same chart is created using the field “URL Order of Importance” as dimension, when it is sorted by the field “URL Order of Importance”, the values will show in the following order: ‘my-site-3’, ‘my-site-1’, ‘my-site-2’. If sorted in descending order, the order will be reversed.