Apologies to my blog followers for a long period of silence. Needless to say, I’ve been busy – among other things, working on converting our Masters Summit for QlikView into all new, freshly re-designed Masters Summit for Qlik. The difference may sound subtle, but in reality, a lot of preparation work is happening as we speak. All four Master Summit presenters are working on upgrading their lectures and electronic materials in such a way that can serve both QlikView developers and the new generation of Qlik Sense developers equally well. Using the opportunity, we critically review our presentations, take out materials that weren’t as successful as we expected, and add new content that didn’t fit in before. Wouldn’t be an exaggeration to say that each one of us is “making the list and checking it twice”.

The following material probably won’t make the cut for the new Masters Summit curriculum, however it shares the spirit of our content – advanced, deep, and curiously unusual.

Surely, QlikView and Qlik Sense developers that follow “What’s New” columns in Qlik blogs, have noticed that Qlik Sense (starting from version 2.0) and QlikView 12.0 are now equipped with 5 new system variables that are related to dates and localization parameters:

SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';

I won’t bore you with the description of what these new variables are and what they do. I can’t present them better than Henric Cronstrom already did in his blog here: Redefining the Week Start in Qlik Sense, and in a few related articles listed at the end of the article. The focus of my Q-Tip here is the new variable FirstWeekDay, and specifically the new behaviors that might sneak up on you if you are not consciously waiting for them.

So, with the new parameter, we can finally define the week that’s starting on Sunday and not on Monday (the former is common in North America, while the latter is common in Europe). Moreover, the default setting for North America is now 6, so you can enjoy this wonderful new reality by default, without changing anything and without even being aware of it! With the new setting, the week days will be sorted correctly – Sunday first, and then Monday, Tuesday, etc.

Not only that, but if your company decided to start its week on Tuesday, you can do that, too – and the week days will be sorted correctly again!

What you need to carefully read between the lines is this: If you are used to the fact that 0=Monday, then you might get fooled! In this new reality, Monday can be 1, 2, or even 6. The days are sorted correctly because the day numbers are now SHIFTING!

So, if your existing applications have any conditional logic that’s based on specific week days (this is very common in retail analytics and anywhere else where outcomes strongly depend on work days, weekends, and holidays) – this logic may need to be revisited before you can safely upgrade to Qlik Sense or QlikView 12. The following statement will not necessarily return TRUE for Mondays anymore:

So, what’s the cure? Let’s calculate “the new Monday”. A quick test will reveal that Monday = 1 when FirstWeekDay = 6; Monday = 2 when FirstWeekDay = 5, etc… In other words, Monday = 7 – FirstWeekDay, except for the old default value 0. When FirstWeekDay = 0, Monday = 0 (just as it was in the past). After a bit of pondering, I decided to formulate the new values for the week days as the following:

let vMonday    = MOD(0 + (7-FirstWeekDay), 7); 
let vTuesday   = MOD(1 + (7-FirstWeekDay), 7); 
let vWednesday = MOD(2 + (7-FirstWeekDay), 7); 
let vThursday  = MOD(3 + (7-FirstWeekDay), 7); 
let vFriday    = MOD(4 + (7-FirstWeekDay), 7); 
let vSaturday  = MOD(5 + (7-FirstWeekDay), 7); 
let vSunday    = MOD(6 + (7-FirstWeekDay), 7);

Just to clarify, I used the MOD() function here to avoid the ugly IF() condition that ensures that the end result is a number between 0 and 6. MOD() returns the whole remainder from the division of the resulting number by 7.

Now, our earlier expression that was broken before, can be formulated in a more flexible way:

After going though all this trouble, we realized that we didn’t have to work so hard… There is another solution, which is a lot easier than the one above. In addition to the new system variables, a new optional parameter was added to a number of new functions, most notably, WeekDay() and WeekStart(). With the new parameter, we can now specify the desired week start day within the function, thus overriding the default setting. So, the same function could be “fixed” a lot easier, without going through the process of calculating “the new Monday” (not saying that it wasn’t fun!):

With the new optional parameter set to 0, Mondays will always return zeros!

Alternatively, you can of course preserve your existing logic by reverting the default value of FirstWeekDay to 0, but then you don’t get to enjoy the new and improved presentation of days, from Sunday to Saturday, just as we like it in North America.

Conclusion #1:

The new Week Start functionality simplifies the logic of properly displaying weeks and week days, especially in North America. With that, any existing logic that builds on the specific WeekDay numbers (Monday = 0, for example) may need to be revisited prior to upgrading to Qlik Sense or QlikView 12.

Conclusion #2:

Just as you think that you know enough about QlikView (Qlik Sense), another opportunity to learn something new presents itself. Join us at one of the next sessions of the Masters Summit for Qlik – in Milan, Italy in April, or in Austin, Texas in September, to learn a lot more than you ever thought was possible, about your favorite BI tool!