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? More from the Bitmetric team Take your Qlik skills to the next level! Since 2013, the Masters Summit for Qlik is the premier advanced training for Qlik. Join us in Vienna and take your Qlik skills to the next level. Join the team! Enjoying these challenges? We have many more, and we’ll even pay you to solve them 😉 Check out our job opening. Expressions Friday Qlik Test Prep Script Solution How can we help? Barry has over 20 years experience as a Data & Analytics architect, developer, trainer and author. He will gladly help you with any questions you may have. Call us Mail us 29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution 22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization 11 March 2024 5 ways to pass selections between Alternate States in Qlik Sense This guide delves into the intricacies of passing selections between Alternate States in Qlik Sense, offering a comprehensive look at techniques such as explicit and implicit assignments, merging selections, and utilizing granularity. Perfect for developers and data analysts, it equips you with the knowledge to leverage Alternate States effectively, enhancing dashboard functionality and enriching data analysis. Expressions Friday Qlik Test Prep Qlik
29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution
22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization
11 March 2024 5 ways to pass selections between Alternate States in Qlik Sense This guide delves into the intricacies of passing selections between Alternate States in Qlik Sense, offering a comprehensive look at techniques such as explicit and implicit assignments, merging selections, and utilizing granularity. Perfect for developers and data analysts, it equips you with the knowledge to leverage Alternate States effectively, enhancing dashboard functionality and enriching data analysis. Expressions Friday Qlik Test Prep Qlik