Redefining the Week Start in Qlik Sense

[hic]

Last year I wrote a blog post on how to set Sunday as the first day of the week, instead of using the ISO 8601 default. This is fairly straight forward – all you need to do is to write some formulas in the script. See Redefining the Week Start.

However, if you use Qlik Sense it has become even simpler. You don’t need any custom formulas and usually it just works - without you doing anything.

When you create your Qlik Sense app, a number of environment variables are created in the beginning of the script. These variables are based on regional settings of your computer, so usually you don’t need to change any of them. One of the variables is “FirstWeekDay” and this defines which day of the week you want to use as your first day.

If you want Monday, then you should use

    Set FirstWeekDay = 0; // 0=Mon, 1=Tue, ... , 6=Sun

And if you want Sunday, you should use

    Set FirstWeekDay = 6; // 0=Mon, 1=Tue, ... , 6=Sun

Change it if you need to!

This variable is used as default for several functions, most notably WeekDay() and WeekStart(). So when you call these functions, you will automatically get the correct week start and the correct order of the week days.

If you use FirstWeekDay=6, you will get a result like in the picture above. You can clearly see that both the order of the week days (in the filter pane to the left) and the week starts (in the pivot table) are correctly defined. I have used US settings and the following expressions to define these fields:

    WeekDay( Date )  as WeekDay,

    WeekStart( Date )  as WeekStart,

    WeekDay( WeekStart( Date ) )  as [Day of WeekStart],

The environment variable changes the defaults of these functions, but you can of course also override the default by using an explicit parameter in the function call:

    WeekDay( Date, $(MyFirstWeekDay) )  as MyWeekDay,

    WeekStart( Date, 0, $(MyFirstWeekDay) )  as MyWeekStart,

With Qlik Sense, we have made the date and time management a lot easier.

HIC

PS This functionality does not yet exist in QlikView.

Further reading related to this topic:

Ancient Gods and Modern Days

Roman Emperors and the Month Names

Redefining the Week Start

Redefining the Week Numbers

[paul_scotchford]

Thanks for the tip Henric. The more I do with QlikSense the more I like it for Self Service and my users.

Paul

[Oleg_Troyansky]

This may not be the most appropriate place for my question, but this topic is related to the topic of this article...

I'm stomped, and I hope you can help me ... We all know that 0=Monday in QlikView. It is stated in all Help articles (including this blog article), and it can be easily tested:

When I'm testing the same in Qlik Sense, I'm getting a different result. Both on my own Qlik Sense Desktop and on my client's server, Monday = 1:

Can anyone explain this behavior? Is it something that's expected, and how to control this logic?

Thanks in advance!

Oleg Troyansky

[Oleg_Troyansky]

OK, I figured it out... It is indeed related to this topic. The value of FirstWeekDay impacts the behavior of the WeekDay() function. It is mentioned in the Help article, but in a very subtle way, as part of the example.

This would make another worthy blog article... I'll post it soon.

[Oleg_Troyansky]

So, as you can see from my previous comment, the new feature that allows redefining the Week Start, can get a bit confusing if you are used to the fact that Monday = 0... Please read the full description of the problem and a few suggested solutions in this blog article:

Happy New Year, New Calendar Functions, and New Behaviors!

cheers,

Oleg Troyansky

Take your Qlik skills to the next level at the Masters Summit for Qlik, now with new and redesigned materials!

[medempudi_ravi]

Can I expect similar Behavior in Qlikview Version 12...or is it exclusive to Qliksense?

[Oleg_Troyansky]

Yes, the same behavior has been implemented in QV 12.0 (AFAIK) - you can see the new variables in the beginning of your new QV 12.0 app's script.

[Karim_Khan]

But HIC if i wanted to shows the day numbers starting from 1 not 0 at that time what ll be scenario

[hic]

Then you need to define it using the Dual() function, e.g.

Dual( WeekDay(Date) , 1 + WeekDay(Date)) as WeekDay,

instead of

WeekDay( Date )  as WeekDay,

[Karim_Khan]

Thank you HIC

[#11]

Hi,

My qlikview version is 11.2 and the variable "FirstWeekDay" does not work here.

How should I do to count weeks starting on friday? so that every register has a number from 1 to 53, but thinking that the week starts on Friday

Thanks