Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
Former Employee

## 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.

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.

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.

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:

Div( Date - WeekStart( WeekYearRefDate, 0, \$(vCal_FD) ) + 7, 7 ) as WeekNumber,
Year( WeekYearRefDate ) as WeekYear;
Date( YearStart( If( \$(vCal_BW), Date, WeekRefDate )) + \$(vCal_RD) - 1) as WeekYearRefDate ;
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 &amp; Time

Contributor III

And what about if my company needs week 1 to start on Thu. 01/01/2015, week 2 to to start on Mo. 12/01/2015, week 3 on Mo. 19/01 and so on — i.e. week 1 lasts 11 days?

Is it something I can get tweaking these parameters?

665 Views
Not applicable

hi all

Anyone can help.

If my expression is

IF(Sum(

{\$

<Year = {\$(=Only(Year))}>

}

TotalNetPrice)=0,0,(Sum(

{\$

<Year = {\$(=Only(Year)-1)}>

}

TotalNetPrice)) )

and since i am working "Only Year" , when i select 2014 in a drop list, 2014/12/30 should be week 1 2015, but it appeared in week1 in 2014 instead.

Any way to change the year to 2015?

FYI: and i have adapted the

Date( WeekStart( Date, 1, \$(vCal_FD) ) - Cal_RD ) as WeekRefDate ;

Date( YearStart( If( \$(vCal_BW), Date, WeekRefDate )) + \$(vCal_RD) - 1) as WeekYearRefDate ;

Div( Date - WeekStart( WeekYearRefDate, 0, \$(vCal_FD) ) + 7, 7 ) as WeekNumber,

Year( WeekYearRefDate ) as WeekYear;

665 Views
Not applicable

hic Henric,

Good article.  What about account week, e.g., when the 1st week starts in April, how do you handle that?

665 Views
Anonymous
Not applicable

Hi Henric,

What happend if the first week starts on Febrary 1. I always use this calendar , thanks for your help!

Best Regards

let numeromes=num(Month(Today()));

Let vDateMin = Num(MakeDate(2011, 12));

LET vDateMax = Num(YearEnd(Today()));

REM We first validate that initialization variables have been defined;

Let vStartDateDefined = If(IsNull(vDateMin),

MsgBox('The Start Date for the Master calendar has not been defined (vDateMin).' &

' Please declare it before continuing.',

'Undefined Variable.',

0, 16, 0));

Let vEndDateDefined = If(IsNull(vDateMax),

MsgBox('The End Date for the Master Calendar has not been defined (vDateMax).' &

' Please declare it before continuing.',

'Undefined Variable.',

0, 16, 0));

If vStartDateDefined = 1 or vEndDateDefined = 1 Then

LET vStartDateDefined =;

LET vEndDateDefined =;

Exit Script;

Else

[Master Calendar]:

Year & [Month (#)] as [Period (#)],

AutoNumber(Year & [Month (#)], 'MonthID') as MonthID,

AutoNumber(Year & [Month (#)])            as MonthID2,

Month & ' - ' & Year as Periodo,

'Q' & Ceil([Month (#)] / 3) as Quarter,

AutoNumber(Year & 'Q' & Ceil([Month (#)] / 3), 'QuarterID') as QuarterID,

num(Date)                                                      as NDate    ,

num(Date)                                                      as NDate1   ,

MonthEnd(MakeDate(year(Date),Month(Date),1)) as DayFin,

Day(MonthsEnd(1, Date))                                   as LastDayMonthPrev;

Date(\$(vDateMin) + IterNo() - 1, '\$(DateFormat)') as Date,

Day(\$(vDateMin) + IterNo() - 1) as [Day (#)],

WeekDay(\$(vDateMin) + IterNo() - 1) as WeekDay,

Week(\$(vDateMin) + IterNo() - 1) as Week,

Year(\$(vDateMin) + IterNo() - 1) as Year,

Month(\$(vDateMin) + IterNo() - 1) as Month,

Num(Month(\$(vDateMin) + IterNo() - 1), '00') as [Month (#)]    ,

Num(Month(\$(vDateMin) + IterNo() - 1)) as [Month (#)2],

WeekDay(\$(vDateMin) + IterNo() - 1)& Day(\$(vDateMin) + IterNo() - 1) as [NombreDia],

// **** esto es nuevo *** //

WeekStart( \$(vDateMin), 0, \$(vCal_FD) ) as WeekStart,

Dual( WeekDay( \$(vDateMin) ), Mod( WeekDay( \$(vDateMin) - \$(vCal_FD) ), 7 ) +1 ) as WeekDay2

//

////    Div( \$(vDateMin) - WeekStart( \$(vDateMin), 0, \$(vCal_FD) ) + 7, 7 ) as WeekNumber//,

////        Year( WeekYearRefDate ) as WeekYear,

////        Date( YearStart( If( \$(vCal_BW), Date, WeekRefDate )) + \$(vCal_RD) - 1) as WeekYearRefDate,

////        Date( WeekStart( Date, 1, \$(vCal_FD) ) - \$(vCal_RD) ) as WeekRefDate

AUTOGENERATE 1

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

End If

665 Views
Contributor II

thanks alot.

665 Views
Former Employee

You can find a newer, updated script on Calendars

HIC

665 Views

HIC,

I want to calculate Week number, But week start from Monday. How to achieve this?

Let's assume This year start the day is Friday (1-Jan-16). But, i want to calculate week from Monday that is (4-Jan-16) to (11-Jan-16) as 1st Week

Similar that?? Can anyone help me?

691 Views
Former Employee

The default behaviour is to have Monday as the first day of the week. Further, week one is by default defined as the week that contains Jan 4th. So your case should work straight off.

However, this definition means that week one some years starts already in December. If you don't want this behaviour you need to ask yourself what you want instead. One possibility is that week one should start the first Monday of the year. In other words: Week one always contains Jan 7th. Then you should use

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 = 7; // Reference day = This day in Jan defines week one {1..7}

HIC

691 Views
Creator

Hello HIC,

Thanks for the post! It is very helpful.

I implemented your script and now rolling 53 weeks is working fine.

But what do I compare week 53 to?

Do you have a sample script to achieve this?

Nazira

691 Views
Master II

Hi hic

I've used your calendar script and its working fine for 2016 & 2017 but 2018 week1 not working as expected out (iso week1: 1st jan'18 to 6th jan 18 instead of 1-jan-18  to 7-jan-18 ).

here below is the ISO week format expected week 2018 (week 1 starts from 1-jan-18 to 7-jan-18)

Week Numbers for 2018

FYI,

T_MinMax:

Min(FieldValue('Date',RecNo())) as MinDate,

Max(FieldValue('Date',RecNo())) as MaxDate

AutoGenerate FieldValueCount('Date');

LET vMinDate = num(Peek('MinDate'));

LET vMaxDate = num(Peek('MaxDate'));

LET vToday = vMaxDate;

DROP Table T_MinMax;

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}

MasterCalendar:

Day(CalDate) as Day,

Month(CalDate) as Month,

WeekDay(CalDate) as WeekDay,

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

Date(MonthStart(CalDate), 'MM-YYYY') as MonthYear,

Div( CalDate - WeekStart( WeekYearRefDate, 0, \$(vCal_FD) ) + 7, 7 ) as Week,

Year( WeekYearRefDate ) as Year;

Date( YearStart( If( \$(vCal_BW), CalDate, WeekRefDate )) + \$(vCal_RD) - 1) as WeekYearRefDate ;