Qlik Community

Qlik Design Blog

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

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
Henric_Cronström

Week numbers are often used in calendars, although not as commonly in some countries as in others. In northern Europe, it is very common to refer to a week by its number, but in many other countries it is not used at all. Just as with the week start, week numbers are defined differently depending on country, so you may need to add code in QlikView to generate your own week numbers.

 

So, how do you count the weeks? Is Jan 1st always part of week one? Not necessarily.

 

If week 53 starts as late as Dec 28th, does Jan 1st also belong to week 53? Sometimes, yes.

 

There is a definition made by the International Organization for Standardization (ISO 8601) that QlikView uses to calculate week numbers. It states that

  1. The week starts on a Monday.
  2. A week is always unbroken.
    I.e. some years week 1 starts already in December, and in other years week 52 or 53 continues into January.
  3. Week 1 always contains Jan 4th.
    Or, differently put: Week 1 always has at least 4 days in January. A third way to say the same thing is: The first Thursday of the year always lies in week 1.

 

These three bullets define the three parameters you need to define general week numbers:

 

     Set vCal_FD = 0; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}
     Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}
     Set vCal_RD = 4; // Reference day = This day in Jan defines week one {1..7}

 

How the week start - the first parameter - influences the week number can be seen in the following table. It shows how the week number would change for the days around New Year 2013 if different week starts are used. The other parameters are kept constant.

 

Table x04.png

 

The second parameter concerns whether or not broken weeks should be used. If they are, a new week number will always be used on Jan 1st, and as a consequence the first and last weeks of the year can have less than 7 days.

 

Table 6x4.png

 

And finally, the third parameter, the reference day. It defines which day that always belongs to week 1. In the table below, the reference day is 4; hence Jan 4th always belongs to week 1, which can be clearly seen. This number also defines the minimal number of days of week 1 that fall in the new year.

 

Table 604.png

 

The ISO standard is thus a 0/0/4 week numbering. In countries where Sunday is used as first day of the week, I have seen several different variants: 6/1/1, 6/0/3 and 6/0/4.

 

If you copy the above parameters to your QlikView script and the following lines to your Master Calendar definition, you can redefine the week numbers any way you want:

 

     Load *,
          Div( Date - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,
          Year( WeekYearRefDate ) as WeekYear;
     Load *,
          Date( YearStart( If( $(vCal_BW), Date, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate ;
     Load *,
          Date( WeekStart( Date, 1, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate ;

 

The fields WeekYearRefDate (Jan 4th in the ISO definition) and WeekRefDate (the Thursday of the week in the ISO definition) are really not necessary, but the expressions become somewhat simpler if these are used.

 

Until we get a general week numbering functionality built into the QlikView standard functions (and, yes, we are looking into this) you will have to redefine the week numbers using the above script. Good luck!

 

HIC

 

Further reading related to this topic:

Ancient Gods and Modern Days

Redefining the Week Start

Redefining the Week Start in Qlik Sense

Qlik Sense – Date & Time

43 Comments
schernov
Creator
Creator

Thanks, HIC! Very useful info!

0 Likes
8,065 Views
schernov
Creator
Creator

How you make this table in QlikView, HIC ? http://community.qlik.com/servlet/JiveServlet/showImage/38-3545-52096/Table+604.png

With 3 lines in head? Year, Month, Day

using Load Inline ?

share qvw-file please?

0 Likes
8,065 Views
MK_QSL
MVP
MVP

Very useful post...

0 Likes
8,065 Views
eruditio
Partner
Partner

Great! I've been willing to ask about that

Getting 31/12/12 in week 1 was kind of bizarre

0 Likes
8,065 Views
carbal1952
Creator II
Creator II

Good explanation. Clear & deep, as always.

Thank you again.

CB.

0 Likes
8,065 Views
Not applicable

Why northern europe people so special like to use week number , any special advantage ? it is more precision to said let us meet at 4 Jan or Week one monday ? hope some europe people here can enlighten me.

0 Likes
8,065 Views
Henric_Cronström

@ Stanislav Chernov : It is just a matter of dragging and dropping the header of a dimension in a pivot table to that position.

@ Antoine Frangieh :  "Bizarre"? Well it depends where you're coming from. To me, it is bizarre that a week can have one number in the beginning and an other in the end. If you want unbroken weeks, you will inevitable get some weeks where parts belong to the "wrong" year.

@ paul yeo : All the calendars - both paper and electronic - have week numbers in them. So when you discuss when to have a meeting, you look in your calendar and say "what about week 5?" instead of saying "what about the week starting Monday the 31st of January?" It's shorter and to the point. Apart from that, there is no advantage...

HIC

5,596 Views
Not applicable

I use WeekNumbers for YOY comparison. You cannot use dates for that because the week end and week starts do not align.

I use a line chart with 2 dimensions, the week number in year (1-52) and year so you can see accumulated sales by week. You can also do this if you set up a WeekNuminQuarter field, Qtr over Qtr comparison.

For the values that fall into the next year or quarter I assign them to week 52 in year and in quarter I assign them to week 13. These values have always been so minimal but having a Week 53 with 1 day or a week 13 with 1 day in a chart doesnt look that great.

0 Likes
5,596 Views
Henric_Cronström

You should perhaps define your weeks as arbitrary 7-day periods counted from the beginning of the quarter:

     Div(Date-QuarterStart(Date),7) + 1 as QuarterWeek


HIC

0 Likes
5,596 Views
colin_albert

Great post. Week numbers always cause confusion when customers ask for reports by week no. Often they have not considered issues such as week start day short week 1 etc. Your post concisely clarifies these points.

When I have calendars that use weeks, it is worth clearly identifying WeekYears with a W prefix so users are clear they are selecting a WeekYears not calendar year.

Dual('W' & Year( WeekYearRefDate ), Year( WeekYearRefDate ) ) as WeekYear


5,596 Views