14 April 2022 Date formatting and interpretation functions in Qlik Share this message 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? Want more? Then click the button below for our full archive! More Qlik test prep questions and answers Expressions Friday Qlik Test Prep Script Solution How can we help? Feel free to contact us if you have any comments or questions. Call us Mail us 27 December 2022 Calculating fractiles 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 calculating fractiles in Qlik. It seems like a lot of you have spend a great time around Christmas, since we are normally used to more answers. […] Friday Qlik Test Prep Solution 21 December 2022 Sorting data in Qlik Sense 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 sorting data in Qlik Sense. Many responses again and as expected nobody was fooled by the answers. You all got it right! The correct answer is […] Friday Qlik Test Prep Qlik 14 December 2022 Using ApplyMap in Qlik Sense 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 using ApplyMap in Qlik Sense. Great to see so many responses and correct answers again. Luckily ApplyMap seems to be a very familiar function. The correct […] Friday Qlik Test Prep Solution
27 December 2022 Calculating fractiles 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 calculating fractiles in Qlik. It seems like a lot of you have spend a great time around Christmas, since we are normally used to more answers. […] Friday Qlik Test Prep Solution
21 December 2022 Sorting data in Qlik Sense 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 sorting data in Qlik Sense. Many responses again and as expected nobody was fooled by the answers. You all got it right! The correct answer is […] Friday Qlik Test Prep Qlik
14 December 2022 Using ApplyMap in Qlik Sense 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 using ApplyMap in Qlik Sense. Great to see so many responses and correct answers again. Luckily ApplyMap seems to be a very familiar function. The correct […] Friday Qlik Test Prep Solution