Date formatting and interpretation functions in Qlik

Learn about the Date() formatting function and the Date#() interpretation function, and what is the difference.

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about Qlik date interpretation functions:

A Data Architect needs to build a Sales Dashboard. The data is stored in CSV files which store dates in a YYYYMMDD format. An example is shown in figure 1. As this format differs from the DateFormat specified in the script, the dates are not correctly interpreted as dates by Qlik. Which function should the Data Architect use to correct this issue?

The correct answer is A: Date#()

In this case we have to use Date#() to tell Qlik to interpret the Date field as a Date value. This is different from Date() in which we tell Qlik how to format the date value.

So what’s is the difference between Date() and Date#() in Qlik?

To explain this, we first need to understand that the date field in Qlik has a dual value. All dates in Qlik are stored as a number, which is then represented as a text value in the chosen date format. To illustrate this, we will take a look at the table below:

Qlik table showing how different date formats are interpreted.

All the way to the left is the Date field we loaded from the CSV file. If we convert this using Date() in the column next to it, it shows a weird date far away in the future. Now converting that field to a number using Num() we see that the value stays the same as the original. This means that the field Date showing 20200101 is actually a numerical value.

Now let’s find out which numerical value corresponds to the date we are looking for!

Using MakeDate(2020,1,1) we create a date field for January 1st 2020, and behold, this is working as expected. Then, subsequently, turning that into a number value using Num(), we get a result showing the underlying numerical value of 43831 stored within Qlik. This number represents the 1st of January 2020. Now understanding this, it makes perfect sense for Qlik to show us a date of 29 December 57205, since 20200101 is interpreted as a numerical value and only formatted to a date format, using the Date() function.

Therefore, we have to make Qlik understand that 20200101 is indeed the 1st of January using the Date#() syntax on the Date field:

Date#(Date, ‘YYYYMMDD’)

Using this syntax, we tell Qlik how to interpret the value of the Date field and that it is indeed a date value, not a numerical value.

In the first part of the syntax we declare the field Qlik has to interpret, in the next part of the syntax, we tell Qlik the formatting in which the date is stored. In this example YYYYMMDD.

Qlik will now know the correct date value, but the formatting remains unchanged from the input of YYYYMMDD. To fix this, we can wrap the result in the Date() function:

Date(Date#(Date, ‘YYYYMMDD’), ‘DD-MM-YYYY’)

That’s it for this week. See you next Friday?