Qlik Community

Qlik Design Blog

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

Redefining the Week Numbers

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

42 Comments
schernov
New Contributor III

Thanks, HIC! Very useful info!

0 Likes
2,577 Views
schernov
New Contributor III

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
2,577 Views
MVP
MVP

Very useful post...

0 Likes
2,577 Views
Partner
Partner

Great! I've been willing to ask about that

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

0 Likes
2,577 Views
carbal1952
Contributor II

Good explanation. Clear & deep, as always.

Thank you again.

CB.

0 Likes
2,577 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
2,577 Views

@ 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

2,577 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
2,577 Views

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
2,577 Views

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


2,577 Views
Partner
Partner

Great post. Thanks.

I take it this would also work if you wanted the Fiscal Week No where the fiscal year does not start in January, and that you could either pass a modified date instead of the Date field Eg AddMonths(Date, 6), or you could use the 3rd parameter in the YearStart function to set the 1st month of the year.

0 Likes
2,577 Views
Not applicable

Some of the functions have to be tweaked a bit when working with week # because of the overlap in months, quarters and years but I find that using these dimensions is so important in period over period comparative analysis.. It has been awhile since I worked on these apps.

Debbie Pyykkonen

Archipelago IS, LLC | Director, Business Analytics| www.archipelagois.com

303 S. Broadway, Suite 200-169 | Denver, Colorado 80209

Twitter | LinkedIn | Facebook |

(: 303-304-6425 | *: dpyykkonen@archipelagois.com

Check out our Workshops and Events!

Download QlikView for FREE!

0 Likes
2,577 Views

Having weeks months and calendar years on the same chart (or even sheet) can be a recipe for confusion. I would keep charts that use weeks and WeekYear separate from charts using months and calendar years.

0 Likes
2,577 Views
Not applicable

I agree with the weeks in months but QOQ and YOY the discrepancies are minimal.

many companies want to compare their data YOY or QOQ on a weekly basis. the only way to do this is with #’s not dates. I agree, months is taking it too far, move to Days at the month level but Quarters and Years work very well especially for sales analytics for those businesses that run on a quarterly/annual quota.. It really helps to drive the sales managers to push to meet their quota when they look at this analysis. we used it religiously to fine tune forecasting because all of our sales team was held accountable for forecasting accuracy. when they see how their sales trend on a weekly basis QOQ, they start to forecast better.

QlikCommunity

Redefining the Week Numbers

new comment by Colin Albert - View all comments on this blog post

Having weeks months and calendar years on the same chart (or even sheet) can be a recipe for confusion. I would keep charts that use weeks and WeekYear separate from charts using months and calendar years.

Reply to this email to respond to Colin Albert's comment.

Following Redefining the Week Numbers in these streams: Inbox

© 1993-2014 QlikTech International AB Copyright & Trademarks | Privacy | Terms of Use | Software EULA

>

0 Likes
2,577 Views

Michael Solomon : I wouldn't pass a modified date, because it would not be maintainable code (although I think you're right that it would work). I am right now investigating different ways of shifting the week-year and I am sketching on a third blog post in this series; one about financial years. It won't be for next week, but sometime later.

However I can already now say that if you want to shift the year a specific number of days, you can set the third parameter, the vCal_RD to any integer value: E.g. if you set it to -1, then Dec 30 will always be in week one.

HIC

0 Likes
2,577 Views
Sajid_Mahmood
Contributor

Great logic for finding the week nos.

Regards

SM

0 Likes
2,577 Views
Not applicable

Thank u ! Very useful Post.

Regards,

Divya

0 Likes
2,577 Views
Partner
Partner

Hi HIC,

Thanks for providing such a idea, really very nice post.

0 Likes
2,577 Views
Not applicable

Hi Henric,

This is a very nice post!

I have a question related to this topic: How would you manage to change the month of the week?.

Imagine a week that is part of 2 months. in my requirements, this week should be taken into account for the month for which the week has more days in.

i.e.:    Mon 30/01/2013   in Qlikview: Year = 2013 Month = 12 Week = 1

          Tue  31/01/2013

          Wed 01/01/2014  in QlikView: Year = 2014 Mnth = 1 Week = 1

          Thu 02/01/2014

          Fri 03/01/2014

          Sat 04/01/2014

          Sun 05/01/2014

So, last days of 2013 should be part of january 2014 .

Inside a same year, I can manage it but I still have the probleme for the last/first week of the year .

Your help would be great !

Thanks

CaroM.

0 Likes
2,577 Views

If you want the entire week to belong to the month in which the week has 4 or more days, you could easily calculate the month from the 4th day of the week. I.e.

Month(WeekStart(Date)+3) as WeekMonth,

Date(MonthStart(WeekStart(Date)+3),'YYYY-MM') as RWeekMonth,

HIC

0 Likes
2,577 Views
Partner
Partner

Hi

How do I deal with this scenario

Where Week1 is : Sundays, in June:

03/06/2012
02/06/2013
01/06/2014

Note: the week start on a Sunday also, so using

vCar_FD = 6;  // 6 being sunday

(DateNum,0,$(vCar_FD)) as [W/C],

(DateNum,0,$(vCar_FD)) as [W/E], //change week End to Saturday

0 Likes
2,577 Views
Not applicable

This was most helpful.  Feel fortunate to have not dealt with this issue before this fairly recent post was created.  However, is your script upside-down?  Also, script newbies might get confused with your use of 'Date' since it's a fairly common key word.  This is what wound up working for me.

JOIN (yourCalendar)

     Load *, Date( WeekStart( yourDate, 1, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate

Resident yourCalendar;

JOIN (yourCalendar)

     Load *, Date( YearStart( If( $(vCal_BW), yourDate, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate

Resident yourCalendar;

JOIN (yourCalendar)

     Load *, Div( yourDate- WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,

          Year( WeekYearRefDate ) as WeekYear

Resident yourCalendar;

0 Likes
2,577 Views

Yes, you could say that my script is upside-down. On purpose.

I believe that you at almost any price should avoid joins in the script, because they easily introduce errors by duplicating records that shouldn't be duplicated. There is however an alternative, much better solution: Preceding Load. But then it looks as if the script is upside-down.

Read more here: http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/04/preceding-load

HIC

0 Likes
2,577 Views
Not applicable

Is it just me or the weekNumber is returning very funny figures. Please help.

0 Likes
2,577 Views
Not applicable

hello thx Henric,

but how to change the parameter if i want  week 1 is first monday in April?

Many thx

0 Likes
2,577 Views
Not applicable

How would you counteract the effect though of a leap year? So if you needed your week 1 to start on the 1st of March this would fall on a different day number dependent on whether or not the year is a leap year or not...is it better then to count backwards and use a negative number in that case?

0 Likes
2,577 Views

Well spotted.

The above calculations will work as long as the reference day refers to days from New Year's eve. So if you set it to 60, March 1st will belong to week one - but only for non-leap years... For leap years it will usually work, but not always.

But there is a solution: Introduce a fourth parameter: A month offset for fiscal years. Basically this would be the same as described in my previous blog post Fiscal Year.

Bottom line: This blog post describes Calendar year + Week numbers.

The previous blog post about Fiscal Year describes Fiscal year + Month number.

For the combination Fiscal Year + Week number, I need to write a third blog post describing how to modify the above formulae using the forth parameter. Maybe I'll do that...

HIC

2,577 Views
Not applicable

Paul,

It's useful when you are tracking something on weekly basis. Like we track that couriers (from branches to Head Office of the company) are not sent more than twice in a week unless urgently required.

0 Likes
2,577 Views
Not applicable

Thanks Henric .. made my day

0 Likes
2,577 Views
jenmclean
New Contributor III

When I try to divide into 13 pay periods, PP13 shows the whole year and it generates a PP14.

PP13 goes from year to year so not sure how to incorporate the Broken Year variable.

The rest of my script is working correctly.

Thoughts?

Load *,
Div( Date - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,
'PP' &
ceil(Div( Date - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) / 4) AS Period,
// If( $(vCal_BW), Date, WeekRefDate ), 'PP' & ceil(Div(Date - WeekStart( WeekYearRefDate, 0, $(vCal_FD) )+ 7, 7 )/4) AS Period,
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 ;

0 Likes
2,577 Views