Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
Henric_Cronström

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
Specialist
Specialist

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
1,991 Views
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:

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
1,991 Views
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.

0 Likes
1,991 Views
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!

0 Likes
1,991 Views
medempudi_ravi
Contributor II
Contributor II

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

0 Likes
1,991 Views
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.

1,991 Views
Karim_Khan
Creator III
Creator III

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

0 Likes
1,356 Views
Henric_Cronström

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
1,356 Views
Karim_Khan
Creator III
Creator III

Thank you HIC

0 Likes
1,356 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
1,356 Views