Redefining the Week Start

[hic]

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.

    

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.

    

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

[christian77]

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.

[hic]

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

[christian77]

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.

[#5]

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

[stevedark]

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

[#7]

Hi HIC Very useful Post .thank u!!!

[#8]

This is very helpful! Thank you.

[Dalton_Ruer]

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.

[hic]

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

[edddddy24]

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