Qlik Community

Qlik Design Blog

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

Redefining the Week Start

Six days thou shalt work, but on the seventh day thou shalt rest.

                                                                             [Exodus 34:21]

The idea that you should rest on the seventh day is a central concept in both Christianity and Judaism. But which weekday is the seventh day of the week? And which day is the first?

The old texts of the Abrahamic religions clearly consider the Sabbath – Saturday – as the seventh day of the week. This day is also still today the resting day for Jews around the world. The same texts also describe how Adam was created on the sixth day, which is one of the reasons why Friday is the day of congregation and prayers for Muslims.

Hence, these two religions agree on the numbering of week days: Friday is the sixth day of the week and Saturday is the seventh.

However, in the rest of the world, the situation is more confusing: Although Sunday is observed as resting day in most countries, there is a disagreement on whether Sunday is the first or the seventh day of the week. In North America, Sunday is the first day of the week, but in many European countries it is the last day of the week. According to the International Organization for Standardization (ISO 8601), the week starts on a Monday, and Sunday is thus the seventh and last day of the week.

How weekdays are ordered makes a difference in all Business Intelligence applications, most notably in how list boxes and charts are sorted. Note the order of the week days in the list boxes below. In the left one, Sunday is on top of the list and in the right one it is Monday.

List box Sunday.png     List box Monday.png

This difference can also be seen in calendar displays, used in many types of software and on many sites in the web. Again, note the order of the week days.

SMTWTFS.png     MTWTFSS.png

QlikView uses ISO 8601 to define the week days and the week start. WeekDay() returns the name of the week day (as defined in the variable DayNames) and a number from 0 to 6;  Monday being day number 0. WeekStart() returns the date of the Monday immediately before the date supplied as parameter.

If you want redefine this, e.g. if you want QlikView to show Sunday as the first day of the week – here’s how you do it:

Start by creating a variable that defines the beginning of the week:

     Set vCal_FD = 6; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

The WeekStart() function has an offset parameter, and if you use your variable here, you can redefine how the function works and get the week start on the correct day:

     WeekStart( Date, 0, $(vCal_FD) ) as WeekStart

The WeekDay() function, however, cannot take any offset parameter, so you need to define the week day using the Dual() function:

     Dual( WeekDay( Date ), Mod( WeekDay( Date - $(vCal_FD) ), 7 ) +1 ) as WeekDay

Using these two expressions in your Master Calendar instead of the standard function calls, you can redefine the week start to any of the week days.

HIC

Further reading related to this topic:

Ancient Gods and Modern Days

Redefining the Week Numbers

Redefining the Week Start in Qlik Sense

26 Comments
narband2778
Contributor II

interesting....

thanks for posting....

0 Likes
739 Views
Employee
Employee

Thank you Henric, very enlightening as usual.

0 Likes
739 Views
Not applicable

Thank you Henric.  You have helped me out with questions I had on Master Calendar before.  I have it in different responses and want to put it all together.  I was wondering if you could help me out providing me an example that combines all the steps together when creating a Master Calendar.  The best way, variables, etc.  I am going to give you the scenario and if you can fill in the gaps that would be awesome.  I have included some comments about the field in bold but they are not in script.

GOAL:  To have three charts.  One for each of the Date Fields from my Load Statement.  The charts would be how many new IP's were created, how many IP's had their first training session, and how many IP's did we have go live.  We might have 10 IP's created, 15 IP's had their first training session, and 4 IP's went live.  The 15 IP's is strictly looking at the "Initial_Training_Scheduled_For__c" so it is capturing the actual number of trained IP's for that month regardless of when those IP's we created.

I hope this makes sense.

Here is my Load Statement:

Accounts:

LOAD Id as AccountId,

     Name as OrganizationName,

     Industry

   FROM

Account.qvd

(qvd);

ImplementationPlans:

LOAD Id as IPID,

     CreatedDate,  //This is the date the IP was Created//

     Initial_Training_Scheduled_For__c, //This is the date the IP was first Trained//

     Go_Live_Date__c //This is the date the IP was Live//

     FROM

Implementation_Plan.qvd

(qvd);

MasterCalendarIPCreate:

LET vDateMin = Num(MakeDate(2012,1,1));

            LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

            LET vDateToday = Num(Today());

           

            TempCalendar:

            LOAD

             $(vDateMin) + RowNo() - 1 AS DateNumber,

             Date($(vDateMin) + RowNo() - 1) AS TempDate

            AUTOGENERATE 1

            WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

            MasterCalendar:

            LOAD

            TempDate AS CreatedDate,

            Month(TempDate) AS CalendarMonth,

            Year(TempDate) AS CalendarYear,

            Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

            RESIDENT TempCalendar ORDER BY TempDate ASC;

            DROP TABLE TempCalendar;

MasterCalendarIPCreate:

LET vDateMin = Num(MakeDate(2012,1,1));

            LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

            LET vDateToday = Num(Today());

           

            TempCalendar:

            LOAD

             $(vDateMin) + RowNo() - 1 AS DateNumber,

             Date($(vDateMin) + RowNo() - 1) AS TempDate

            AUTOGENERATE 1

            WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

            MasterCalendar:

            LOAD

            TempDate AS Initial_Training_Scheduled_For__c,

            Month(TempDate) AS CalendarMonth,

            Year(TempDate) AS CalendarYear,

            Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

            RESIDENT TempCalendar ORDER BY TempDate ASC;

            DROP TABLE TempCalendar;

MasterCalendarIPLive:

LET vDateMin = Num(MakeDate(2012,1,1));

            LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

            LET vDateToday = Num(Today());

           

            TempCalendar:

            LOAD

             $(vDateMin) + RowNo() - 1 AS DateNumber,

             Date($(vDateMin) + RowNo() - 1) AS TempDate

            AUTOGENERATE 1

            WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

            MasterCalendar:

            LOAD

            TempDate AS Go_Live_Date__c,

            Month(TempDate) AS CalendarMonth,

            Year(TempDate) AS CalendarYear,

            Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

            RESIDENT TempCalendar ORDER BY TempDate ASC;

            DROP TABLE TempCalendar;

I believe I have correct syntax for the above Load Statement.    I have included a copy of my Dashboard view to help you out. 

1. I created a ListBox for Industry ( I used the Industry Field)

2. I created a ListBox for Years (I used the CalendarYear Field)

3. I created a ListBox for Months (I used the CalendarMonth Field)

4.  The charts are for the three date fields I want to return results on:

          a.  Total IP's Created

          b.  Total IP's First Training

          c.  Total IP's Live

This is where I am still a little confused.  How do I show results based on The Year, The Month, or Both depending on what options are selected?  My guess is through the Dimension and the Expression on each of the charts but not sure what it would look like.  I have tried different ones and must be using the wrong fields or calculations.  Can you give me some insight?

Thanks

David

0 Likes
739 Views
MVP & Luminary
MVP & Luminary

Thanks for posting Henric.

The other difference that comes about is what WeekNumber any given date falls into.  I believe to resolve this shifting the date you are checking for by the correct number of days can shift week number correctly, eg:

Week(Date+1) as [Week No],

WeekYear(Date+1) & '-' & Week(Date+1) as [Year Week],

As you say, encapsulating this into your Master Calendar makes a lot of sense.

The one could be replaced by a calculation based on your First Day variable as well, if required.

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

0 Likes
739 Views
carbal1952
Contributor II

Thanks once more HIC.

Dates are always a problematic data.

CB.

0 Likes
739 Views

Steve

The problem around Week Numbers will come in an other post...

HIC

739 Views
MVP & Luminary
MVP & Luminary

I shall look out for that one!

0 Likes
739 Views
Not applicable

Bookmarked!! thanks HIC

0 Likes
739 Views
Not applicable

Hi HIC,

I posted my question to the wrong post.  I have added my question to the Master Calendar question I had out there already.  If you can respond to that one instead that would be awesome.

David

0 Likes
739 Views
sudeepkm
Valued Contributor III

Very nice and interesting post with a great background. Thanks a lot HIC.

0 Likes
739 Views
Partner
Partner

History is not an exact science.

History is written for those who won the wars.

Week analysis is a pain on the neck. It's so difficult to compare with last year week. 7 is a prime number, it does not match with anything.

What I'd do? A whole new completely economical calendar.

12 months of 30 days =360 days.

5 weeks of 6 days. Mon, Tue, Wed, Fri, Sat, Sun * 6 = 30 days.

+

a 5 or 6 days month long, Thu, Thu, Thu, Thu, Thu, Thu, which will be used for spiritual retirement. No economic activity those 5 days. Buy everything in advance and blah, blah...

Only hospitals, minimun services.

Then, we will have a nice and easy week analysis.

It's boring but efective.

It's not restricted by any religion but it fullfils with all.

Fisrt of January wil be always Monday.

0 Likes
739 Views

There have been a couple of experiments with alternative calendars, e.g. in France after the revolution - The French Republican Calendar - and in Soviet Union after the revolution - The Soviet Calendar. Both ended because the public support wasn't really there. So, I think we will need to stick to the Gregorian calendar and the 7-day week for yet some time.

HIC

0 Likes
739 Views
Partner
Partner

That's very interesting. Somehow my idea is simpler. It goes with QlikView philosophy, simplify your life, and your boss life,..

Now, what would be complicated is having both at the same time. 

But the simplest of simplest is not to analize by the week.

0 Likes
739 Views
Not applicable

HIC nice post , Now i understand why my iphone month calendar start from sunday and my sumsung note 3 calendar start from monday. Paul

0 Likes
739 Views
MVP & Luminary
MVP & Luminary

Prior period comparisons are always fraught with issues, yet everyone seems to put lots of store on them.  One that is always common is comparing on month to the prior month, yet in retail or other sector where weekends you could be comparing a four weekend month with a five weekend month.  Businesses where seasonality is a big issue (ice cream vendors?) they will factor this into comparisons, but then something will raise its head to mess it up - perhaps Easter which shifts around the calendar with a fair degree of movement.  Week on week comparisons can be more robust, as each seven days will always have the same number of each days, but you have to be aware if someone selects to compare weeks in a specified month if you haven't coded to show weeks that commence in that month you could compare a whole week with a week that is truncated by the month end.

All of these things can be coded for, one way or another, and some businesses will have ways that they approach prior periods that factor these things in (perhaps applying a weighting for weekday to smooth things out) but you do need to be very careful when presenting these comparisons in black and white (or Red and Green if you highlight variance).

Fortunately QlikView has a number of functions, such as the ones Henric has highlighted, that make it easier to deal with these things.

Steve

0 Likes
739 Views
Not applicable

Hi HIC Very useful Post .thank u!!!

0 Likes
739 Views
Not applicable

This is very helpful! Thank you.

0 Likes
739 Views
Employee
Employee

Not sure what I'm doing wrong. I followed the code and in a table box it shows me that the Sunday dates are indeed the start of the week yet the calendar object still shows Sunday on the far right instead of showing Sunday on the left of the calendar as I thought it should after applying the code.

SundayCalendar.jpg

0 Likes
739 Views

You are probably not doing anything wrong. It is a known limitation that the calendar object always has Monday as the first day of the week.

The blog post is about redefining the fields in the Master Calendar, so that functions like WeekStart return the right values. But these do not affect the Calendar Object.

HIC

0 Likes
739 Views
edddddy24
New Contributor II

Hello everyone.

i have  been looking for the answer for some fix that i need to add to my app in this and other pages of the commuity about how to add a column that shows another  order in days, i mean if the normal order is Monday=1, i want thursday=1; and another column that says, taking the new order, the new count week starting in the first thursday of the year.

another task that i need is that , taking as reference the _FlagLYTD field , add 1 day to february only on leap year.

Here i show you my calendar code:

MasterCalendar:

LOAD

  *,

  dual(Year & '-' & Month,Date(Floor(MonthStart(Date))))  as [Year-Month]

;

LOAD

    Date                            as Date,

    Day(Date)                            as Day,

    Weekday(Date)                            as Weekday,

    Year(Date)                                                as Year,

    Month(Date)                            as Month,

    'Q' & Ceil(Month(Date) / 3)                            as Quarter,

    AutoNumber(MonthStart(Date),'_MonthSerial')            as _MonthSerial,

    AutoNumber(QuarterStart(Date),'_QuarterSerial')            as _QuarterSerial,

    AutoNumber(weekyear(Date) &'|' & week(Date),'_WeekSerial') as _WeekSerial,

  If(Num(Date) <= $(_maxBillingDate), 1,0)                    as _FlagBilling,

  Year2Date(Date,-1)                                          as _FlagLYTD

;   

LOAD date('$(_startDate)' + recno() - 1) as Date

AUTOGENERATE date('$(_endDate)') - date('$(_startDate)') + 1

;

where startday and endday is the min and max day of my information.

i hope i was clear enough, sorry for the bad english skills and thanks for you time

0 Likes
739 Views

Take a look at the scripts found on Calendars. In them, there is a parameter where you can define which day to use as the first day of the week.

HIC

0 Likes
739 Views
vamshi87
New Contributor II

Hi Henric,

Has something changed in QlikView 12 to consider the system settings? The reason I am asking is tmy initial default script got generated as below:

SET FirstWeekDay= 6;

I had to manually change this to SET FirstWeekDay =0 to make Monday the First Day of Week.

0 Likes
739 Views

This variable is new for QlikView 12. It didn't exist in QlikView 11.

When you create a new document, QlikView will fetch this information from the system settings of your computer. So, if you have Sunday as first day of week there, you will get it in your QlikView document too.

HIC

0 Likes
739 Views
vamshi87
New Contributor II

Thanks Henric!

0 Likes
739 Views
Or
Valued Contributor II

Hey Henric,

I also noticed this change, but it doesn't seem to work as expected, at least - not with WeekName().

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=4;

No matter what I set the ReferenceDay and BrokenWeeks to, weeks are numbered in the same manner - Week 2017/52 contains December 31st through January 6th. Obviously this is 'incorrect' - if the reference day is 4, it is my understanding that the week containing January 4th should always be week 1.

Near as I can tell, both BrokenWeeks and ReferenceDay don't actually do anything - I set them to 0 and no change, I set ReferenceDay to 11 and no change in week numbering. So - FirstWeekDay does seem to work correctly (the week is starting on a Sunday), but it throws off the week numbering if used.

0 Likes
739 Views

Thanks for letting me know. It sounds like it doesn't do what it is supposed to. I'll take a look at it.  /HIC

0 Likes
739 Views