Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Redefining the Week Start in Qlik Sense

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.

Image9.png

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

11 Comments
paul_scotchford
Valued Contributor

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

Paul

0 Likes
63 Views
MVP
MVP

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:

Monday in QlikView.PNG

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:

Monday in Qlik Sense.PNG

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

Thanks in advance!

Oleg Troyansky

0 Likes
63 Views
MVP
MVP

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.

0 Likes
63 Views
MVP
MVP

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!

0 Likes
63 Views
medempudi_ravi
New Contributor II

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

0 Likes
63 Views
MVP
MVP

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.

63 Views
karimkhan
Contributor II

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

0 Likes
63 Views
Employee
Employee

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,

0 Likes
63 Views
karimkhan
Contributor II

Thank you HIC

0 Likes
63 Views
Not applicable

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

0 Likes
63 Views
Employee
Employee

This variable has a  special meaning in Qlik Sense and in QlikView 12. In earlier versions of QlikView you can however make these definitions "manually" in the script.

See

Redefining the Week Start

Redefining the Week Numbers

Recipe for a 4-4-5 Calendar

HIC

0 Likes
63 Views