It is easy to ignore the configuration of an application, commonly referred to as the ‘Main’ section in the Data Load Editor, which is included by default in all new apps.
Truly understanding how this works can improve end-user experience as it provides the developer a means to set the default behaviour and formats of things like money, date formats and first day of the week. It is considered best practice to take the time required to look through this section (shown below) and modify the variables to suit the end-users needs.
Note: The values displayed below may differ to yours. This is because they are based on the locale of the server. As you can see in the code below, the server is set to en-GB. Providing your machine/Qlik Sense server is properly configured, most of the settings typically won’t need changing.
Each line is a single setting within the application. Each setting is application-wide, therefore it will only affect that specific application. Currently, there is no method to set these settings environment-wide. Having said that, you could store the setting externally and use the INCLUDE statement to insert the variables into each application. There are numerous advantages and disadvantages to these which I’m not going to cover in this post, as I want to focus on what each one affects.
| Configuration variable | Description | Recommended use-case |
|---|---|---|
| SET ThousandSep=’,’; | The identifier for a thousand separator will be represented by this value. For example, if the value is a comma the value one million will be shown as 1,000,000 | |
| SET DecimalSep=’.’; | The identifier for a decimal place will be represented by this value. For example, the value one (1) will be shown as 1.00 | |
| SET MoneyThousandSep=’,’; | One million will be shown as 1,000,000 | The thousands separator defined replaces the digit grouping symbol of the operating system (regional settings). For example, if you change the value to a closed square bracket (‘]’), the output would be shown as ‘1]000]000’. |
| SET MoneyDecimalSep=’.’; | One will be shown as 1.00 | The decimal separator defined replaces the decimal symbol of the operating system (regional settings). For example, if you change the value to a comma (‘,’), the output would be shown as ‘1,00’. |
| SET MoneyFormat=’£#,##0.00;-£#,##0.00′; | One hundred punds sterling will be shown as £100.00 | The symbol defined replaces the currency symbol of the operating system (regional settings). For example, if I change the value to ‘$#,##0.00;-$#,##0.00’, the output would show as ‘$100.00’. Note that everything before the semi colon is the pattern for positive values and after is for negative values. To display a negative values with brackets change you pattern to the follows ‘£#,##0.00;(£#,##0.00)’, a negative value will now display as (£100.00). |
| SET TimeFormat=’hh:mm:ss’; | Five minutes past ten will display as 10:05:00 | The format defined replaces the time format of the operating system (regional settings). |
| SET DateFormat=’DD/MM/YYYY’; | November 9th 1989 will display as 09/11/1989 | The format defined replaces the date format of the operating system (regional settings). |
| SET TimestampFormat=’DD/MM/YYYY hh:mm:ss[.fff]’; | Five minutes past ten on November 9th 1989 will display as 09/11/1989 10:05:00 | The format defined replaces the date and time formats of the operating system (as defined by the regional settings). The pattern between the square brackets are optional. |
| SET FirstWeekDay=0; | 0 (= Monday) 1 (= Tuesday) 2 (= Wednesday) 3 (= Thursday) 4 (= Friday) 5 (= Saturday) 6 (= Sunday) |
Integer that defines which day to use as the first day of the week. By default, this is set to Monday, if, for example, you want Sunday as your first day of the week then you would set this variable to 6. |
| SET BrokenWeeks=0; | The following values can be used: 0 (=use unbroken weeks) 1 (= use broken weeks) |
The setting defines if weeks are broken or not. By default, Qlik Sense functions use unbroken weeks. This means that: In some years, week 1 starts in December, and in other years, week 52 or 53 continues into January. Week 1 always has at least 4 days in January. The alternative is to use broken weeks. Week 52 or 53 do not continue into January. Week 1 starts on January 1 and is, in most cases, not a full week. This blog covers this topic quite nicely. |
| SET ReferenceDay=4; | The following values can be used to set a different reference day:1 (= January 1st) 2 (= January 2nd) 3 (= January 3rd) 4 (= January 4th) 5 (= January 5th) 6 (= January 6th) 7 (= January 7th) |
The reference day which day always belongs to week 1 within a year. By default, Qlik Sense sets this to 4. This means that week 1 must contain January 4th, or put differently, that week 1 must always have at least 4 days in January. This blog covers this topic quite nicely. |
| SET FirstMonthOfYear=1; | Valid settings are 1 (January) to 12 (December). Default setting is 1. | This setting defines which month to use as first month of the year, which can be used to define financial years that use a monthly offset. For example, a financial year starting in April would have the value 4. |
| SET CollationLocale=’en-GB’; | en-GB | Specifies which locale to use for sort order and search matching. The value is the culture name of a locale, for example ‘en-GB’, this represents English-Great Britain as opposed to ‘en-US’ which represents English-United States.This is a system-defined variable. |
| SET CreateSearchIndexOnReload=1; | 0 or 1 | This setting should be known by all developers. This setting allows the developer to disable the creation of search index files during a reload, by setting the value to 0. Meaning the application will typically reload faster. This is useful when writing and testing Qlik script. As soon as the script is sound it should be enabled; set to 1. To minimalise user waiting time and frustration all UAT and production apps should have this setting set to 1.td> |
| SET MonthNames = ‘Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec’; | English variation: ‘Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec’ | The format defined replaces the month names convention of the operating system (regional settings). These can be changed to account for different languages. |
| SET LongMonthNames = ‘January;February;March;April;May;June;July;August;September;October;November;December’; | English variation: ‘January;February;March;April;May;June;July;August;September;October;November;December’; | The format defined replaces the long month names convention of the operating system (regional settings). These can be changed to account for different languages. |
| SET DayNames = ‘Mon;Tue;Wed;Thu;Fri;Sat;Sun’; | English variation: ‘Mon;Tue;Wed;Thu;Fri;Sat;Sun’ | The format defined replaces the weekday names convention of the operating system (regional settings). These can be changed to account for different languages. |
| SET LongDayNames = ‘Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday’; | English variation: ‘Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday’ | The format defined replaces the long weekday names convention of the operating system (regional settings). These can be changed to account for different languages. |
| SET NumericalAbbreviation = ‘3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y’; | One billion will be shown as a ‘G’, to change billions to show with a ‘B’ instead you can change the third definition pair to 9:B. | A string containing a list of abbreviation definition pairs, delimited by semi colon. Each abbreviation definition pair should contain the scale (the exponent in decimal base) and the abbreviation separated by a colon, for example, 9:B for a billion. These can most commonly be seen in KPI boxes. |
There are also many additional configurations that can be utilised to improve an application, we promote the use of the following user-defined variables:
| Configuration Variable | Description | Recommended Use-Case |
|---|---|---|
| hidePrefix | All field names beginning with this text string will be hidden in the same manner as the system fields. This is a user-defined variable. If this statement is used, the field names beginning with an underscore will not be shown in the field name lists when the system fields are hidden. | Hide compound keys (common fields created for data modelling purposes and have no business use). Removing fields required for calculations in the UI but the nature of the field itself is sensitive or needs to be strictly limited. |
| NullInterpret | The value between the parenthesis will return null values, only when it occurs in a text file, Excel file or an inline statement. | Removing blank values from Text, Inline and Excel based data sets. |
| NullDisplay | The defined symbol will substitute all NULL values from ODBC, and connectors, on the lowest level of data. | Replacing null values with a user-defined value, the value should be more meaningful. |
| NullAsValue NullValue |
By default, Qlik Sense considers NULL values to be missing or undefined entities. However, certain database contexts imply that NULL values are to be considered as special values rather than simply missing values. The fact that NULL values are normally not allowed to link to other NULL values can be suspended by means of the NullAsValue statement.The NullAsValue statement operates as a switch and will operate on subsequent loading statements. It can be switched off again by means of the NullAsNull statement. | Allowing null values to be used as common values for linking purposes. |
Configuring the application variables can make visualisation creation more efficient by reducing time spent on setting numeric, date and currency formats, as well as assisting in improving the overall experience by instructing the application on how to treat null values and/or hiding irrelevant fields with hidePrefix/hideSuffix.
To stay up-to-date with all the latest Qlik tips and tricks please remember to follow us on LinkedIn, Twitter and YouTube.
By Chris Lofthouse
Follow @clofthouse89