Interpretation Functions

Interpretation functions are used to inform Qlik on how to read a string if it represents a numeric value. These are particularly useful if your source data dates are formatted in a way that Qlik struggles to read by default or you have numerical data stored as strings.

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

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

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

The interpretation 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 a couple of example functions.

Example 1: Date#

The Date#() function is used to tell Qlik how to read a string as a date by specifying the format of the date contained in the string. 

Copy code 


Date#(text[, format])

The rules for reading this are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ().
  2. The various elements you will need to enter are separated by commas. In this function, there are 2 elements: the text and [format].
  3. Anything between square brackets [] is an optional parameter. In this function, the [format] parameter is optional. If omitted, Qlik will assume that the date contained within the string is formatted as per the date system variable. 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-YYYY')

The above function will return the values in the “Date” field formatted as in the source data, ‘DD-MMM-YY’, but Qlik will know that this is a date and will treat it as such when used in visualisations and calculations.

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

Copy code 


Date("Date")

The values in the “Date” field will now be read as though they are in the date system variable format. This may be the case, however, it’s likely that you are using an interpretation function because Qlik is struggling to read the dates thus we would always recommend specifying a format.

Example 2: Money#

The Money#() function converts a string to a specific monetary format so that Qlik can interpret it as a monetary value.

Copy code 


Money#(text[, format[, dec_sep [, thou_sep ] ] ])

The rules for reading this are as follows:

  1. The function name itself is first and all the parameters you need to enter go between brackets: ().
  2. The various elements you will need to enter are separated by commas. In this function, there are 4 elements: the text, [format], [dec_sep] and [thou_sep].
  3. Anything between square brackets [] is an optional parameter. In this function, there are 3 optional parameters. The parameter [format] allows you to specify the format of the monetary value in the string, [dec_sep] allows you to specify the decimal separator and the [thou_sep] allows you to specify the thousand separator. If you do include the parameters, DO NOT include brackets.

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

Copy code 


Money#("Sales", '£#', '.', ',' )

The above function will return the values in the “Sales” field formatted as in the source data but Qlik will know that this is a monetary value and will treat it as such when used in visualisations and calculations.

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

Copy code 


Money#("Sales")

The values in the “Sales” field will now be read as though they are in the system variable format.

How to use interpretation 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:

  • All of the functions can be used throughout the whole load script, not just within a LOAD or SELECT statement
  • They are particularly useful for use during the extract and transform stage of data to ensure that your source data are read correctly by Qlik before data is stored as a QVD file
  • The functions can be nested and combined with other types of functions

A simple example of using the Date#() and Money# functions to read the data correctly is:

Copy code 


SALES:
LOAD
   Date#("Date",'DD-MMM-YYYY') as "Date",
   Money#("Sales",'£#', '.', ',') as "Sales",
   "Customer ID"

FROM [my-data.qvd] (qvd);

The resulting table will have 3 fields: “Date”, “Sales” and “Customer ID”. The “Date” fields will be recognised as a date by Qlik even though it is formatted as a string in the format of ‘DD-MMM-YYYY’ in the source data. Also, the “Sales” field will be read correctly as a monetary value.

How to use interpretation functions in visualisations

Interpretation functions use the same syntax in visualisations as in the load. The general rules are:

  • The functions can be nested and combined with other types of functions.
  • The functions can be used within the dimension and measure calculations.

Here is an example of how to use the combination of the interpretation function Date#() and formatting function Date() in a visualisation to change the format of the date. Note the Date#() is only necessary in this example if Qlik is unable to read the date provided as a date and instead treats it as a string. The functions used in the x-axis are:

Copy code 


Date(Date#(Date,'DD/MM/YYYY'),'YYYY-MM-DD')