Date formatting and interpretation functions in Qlik

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:

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:

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?

More from the Bitmetric team

Qlik Cloud Backup

Protect your investment in Qlik with daily incremental backups stored in an encrypted environment with redundant storage. Available for as little as 2 Euro per day. Learn more.

Join the team!

Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings.