21 February 2022 What’s the value of the Qlik MonthEnd() function? 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 the Qlik MonthEnd() function: This question lead to some spirited debate here at Bitmetric. Is it fair to ask such an ambiguous question? And what is the correct answer? As ambiguous questions are often found in real Qlik certification exams we decided to post this question. Determining which answer the assessor at Qlik wants to hear and picking the least wrong answer are all part of the ‘metagame’ of Qlik certification. It’s good to be prepared for it. The (most) correct answer is B: 28/02/2022 23:59:59 Consulting the official Qlik help documentation shows us that the MonthEnd() function returns a dual data type containing a timestamp: However, the text representation of that dual value is formatted according to the DateFormat rather than the TimestampFormat. So, we have a numeric value containing a timestamp (44620.999999988, the fractional part is the time) and a text value containing a date (28/02/2022). This becomes clear when we wrap the expression in a Num() function: Confusing! 😕 So it’s a matter of preference then? Option A is equally valid! You could make a reasonable case for that, and some of you did. In our opinion though, option A is less right than option B. To illustrate, take a look at the expressions below: Both MonthEnd(Today()) and MakeDate(2022, 2, 28) return ’28/02/2022′. When we compare both values though, we can see that they’re not the same. If you’ve ever had to troubleshoot why a MonthEnd() date didn’t properly associate with your calendar table you will probably agree. Ultimately, in the certification exam, we’re pretty sure (though not 100% sure) that Qlik wants you to demonstrate that you’re aware MonthEnd() returns a timestamp value. The formatting in my app was different from yours The way dates are formatted by default is driven by the DateFormat number interpretation variable in the Main section of your script. By default, this uses the regional settings of your operating system, so formatting can differ between environments. Some people suggested changing the DateFormat variable to include the time. This is an easy way to temporarily show the ‘real’ value of MonthEnd(Today()). We don’t recommend using this as a permanent setting though, as it’ll mess up the display of regular dates, appending 00:00:00 to all of them. Is there a way to solve this ambiguity? In our opinion, as the MonthEnd() function returns a timestamp, it should use the TimestampFormat for the text value. Leaving out the fractional part is akin to the default text representation of the pi() function being: Additionally, the questions on the Qlik certification exam could sometimes be worded with a bit more clarity. There would be a lot less discussion about the right answer if the question was phrased as: “A Business Analyst uses the expression shown in figure 1. If today’s date is February 18th 2022, and the DateFormat number interpretation variable is set to ‘DD/MM/YYYY hh:mm:ss’, what is the result of the expression?“ The lesson: sometimes there isn’t a 100% right answer, just a less wrong one What initially started as a softball question to follow up last week’s tougher question, quickly resulted in some spirited debate here at Bitmetric. It was great to see this same debate occurring in the thread following this question. Thanks to everyone who participated and shared their thoughts! Next Friday we’ll be posting a question that has a more clear-cut answer. See you then? Want more? Then click the button below for our full archive! More Qlik test prep questions and answers Friday Qlik Test Prep Functions 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