The thousands separator defined replaces the digit grouping symbol of the operating
system (regional
settings).
Syntax:
ThousandSep
Qlik Sense apps interpret text fields that conform to this formatting as numbers. This formatting will be displayed in chart objects when a numerical field’s Number formatting property is set to Number.
ThousandSep is helpful when handling data sources received from multiple regional settings.
Information noteIf the ThousandSep variable is modified after objects have already been created and formatted in the application, the user will need to re-format each relevant field by de-selecting and then re-selecting the Number formatting property Number.
The following examples show possible uses of the ThousandSep system variable:
Set ThousandSep=','; //(for example, seven billion will be displayed as: 7,000,000,000)
Set ThousandSep=' '; //(for example, seven billion will be displayed as: 7 000 000 000)
These topics may help you work with this function:
In instances of text field interpretation, the decimal separator settings, as provided by this function, must also be respected.
For number formatting, DecimalSep will be used by Qlik Sense where necessary.
Regional settings
Unless otherwise specified, the examples in this topic use the following date format: MM/DD/YYYY. The date format is specified in the SET DateFormat statement in your data load script. The default date formatting may be different in your system, due to your regional settings and other factors. You can change the formats in the examples below to suit your requirements. Or you can change the formats in your load script to match these examples.
Default regional settings in apps are based on the regional system settings of the computer or server where Qlik Sense is installed. If the Qlik Sense server you are accessing is set to Sweden, the Data load editor will use Swedish regional settings for dates, time, and currency. These regional format settings are not related to the language displayed in the Qlik Sense user interface. Qlik Sense will be displayed in the same language as the browser you are using.
Example 1 - Default system variables
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a table named Transactions.
Use of the default ThousandSep variable definition.
Load the data and open a sheet. Create a new table and add this field as a dimension:date.
Add the following measure:
=sum(amount)
In the properties panel, under Data, select the measure.
Under Number formatting, select Number.
Results table
date
=sum(amount)
01/01/2022
10,000,000,441.00
01/02/2022
21,237,492,432.00
01/03/2022
41,249,475,336.00
01/04/2022
24,313,369,837.00
01/05/2022
47,873,578,754.00
01/06/2022
24,313,884,663.00
01/07/2022
28,545,883,436.00
01/08/2022
35,545,828,255.00
01/09/2022
37,565,817,436.00
01/10/2022
3,454,343,566.00
In this example, the default ThousandSep definition, which is set to comma format (‘,’), is used. In the results table, the format of the amount field displays a comma between thousand groupings.
Example 2 - Changing system variable
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
The same dataset from the first example, which is loaded into a table named Transactions.
Modification of the ThousandSep definition, at the start of the script, to display a '*' character as the thousands separator. This is an extreme example, and is used solely to demonstrate the functionality of the variable.
The modification used in this example is extreme and not commonly used, but is shown here to demonstrate the functionality of the variable.
Load the data and open a sheet. Create a new table and add this field as a dimension:date.
Add the following measure:
=sum(amount)
In the properties panel, under Data, select the measure.
Under Number formatting, select Custom.
Results table
date
=sum(amount)
01/01/2022
10*000*000*441.00
01/02/2022
21*237*492*432.00
01/03/2022
41*249*475*336.00
01/04/2022
24*313*369*837.00
01/05/2022
47*873*578*754.00
01/06/2022
24*313*884*663.00
01/07/2022
28*545*883*436.00
01/08/2022
35*545*828*255.00
01/09/2022
37*565*817*436.00
01/10/2022
3*454*343*566.00
At the start of the script, the ThousandSep system variable is modified to a '*'. In the results table, the format of the amount field can be seen to display a ‘*’ between thousand grouping.
Example 3 - Text interpretation
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a table named Transactions.
Data which has its numerical field in text format, with a comma used as the thousands separator.
Load the data and open a sheet. Create a new table and add this field as a dimension:date.
Add the following measure:
=sum(amount)
In the properties panel, under Data, select the measure.
Under Number formatting, select Number.
Add the following measure to evaluate whether or not the amount field is a numerical value:
=isnum(amount)
Results table
date
=sum(amount)
=isnum(amount)
01/01/2022
10,000,000,441.00
-1
01/02/2022
21,492,432.00
-1
01/03/2022
4,249,475,336.00
-1
01/04/2022
24,313,369,837.00
-1
01/05/2022
4,873,578,754.00
-1
01/06/2022
313,884,663.00
-1
01/07/2022
2,545,883,436.00
-1
01/08/2022
545,828,255.00
-1
01/09/2022
37,565,817,436.00
-1
01/10/2022
3*454*343*566.00
-1
Once the data is loaded, we can see that Qlik Sense has interpreted the amount field as a numerical value, due to the data conforming to the ThousandSep variable. This is demonstrated by the isnum() function, which evaluates each entry to -1, or TRUE.
Information note
In Qlik Sense, the Boolean true value is represented by -1, and the false value is represented by 0.
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!