What’s the value of the Qlik MonthEnd() function?

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?

More from the Bitmetric team

Bitmetric Qlik Support

Keep your Qlik environment running smoothly with proactive support that prevents issues before they appear. Available on flexible monthly plans. Learn more.

Qlik vs Power BI Series

See how Qlik and Power BI perform when theory meets reality. Three apps rebuilt, every step examined. Learn more.