topic Re: Odd behavior in Week function in QlikView

Odd behavior in Week function

Mon, 17 Dec 2012 19:31:50 GMT

I've been playing with the Week function (only because a user requested it) and noticed something quite strange. I'm just wondering if anybody else has noticed that when the last days of December fall on Monday thru Wednesday, the evaluation of Week is returning 1. I loaded a calendar with values staring in 1956, and it shows up repeatedly:

LinkDate	Month	Quarter	Year	Week
12/31/1956	Dec	Q4	1956	1
12/30/1957	Dec	Q4	1957	1
12/31/1957	Dec	Q4	1957	1
12/29/1958	Dec	Q4	1958	1
12/30/1958	Dec	Q4	1958	1
12/31/1958	Dec	Q4	1958	1
12/31/1962	Dec	Q4	1962	1
12/30/1963	Dec	Q4	1963	1
12/31/1963	Dec	Q4	1963	1
12/30/1968	Dec	Q4	1968	1
12/31/1968	Dec	Q4	1968	1
12/29/1969	Dec	Q4	1969	1
12/30/1969	Dec	Q4	1969	1
12/31/1969	Dec	Q4	1969	1
12/31/1973	Dec	Q4	1973	1
12/30/1974	Dec	Q4	1974	1
12/31/1974	Dec	Q4	1974	1
12/29/1975	Dec	Q4	1975	1
12/30/1975	Dec	Q4	1975	1
12/31/1975	Dec	Q4	1975	1
12/31/1979	Dec	Q4	1979	1
12/29/1980	Dec	Q4	1980	1
12/30/1980	Dec	Q4	1980	1
12/31/1980	Dec	Q4	1980	1
12/31/1984	Dec	Q4	1984	1
12/30/1985	Dec	Q4	1985	1
12/31/1985	Dec	Q4	1985	1
12/29/1986	Dec	Q4	1986	1
12/30/1986	Dec	Q4	1986	1
12/31/1986	Dec	Q4	1986	1
12/31/1990	Dec	Q4	1990	1
12/30/1991	Dec	Q4	1991	1
12/31/1991	Dec	Q4	1991	1
12/30/1996	Dec	Q4	1996	1
12/31/1996	Dec	Q4	1996	1
12/29/1997	Dec	Q4	1997	1
12/30/1997	Dec	Q4	1997	1
12/31/1997	Dec	Q4	1997	1
12/31/2001	Dec	Q4	2001	1
12/30/2002	Dec	Q4	2002	1
12/31/2002	Dec	Q4	2002	1
12/29/2003	Dec	Q4	2003	1
12/30/2003	Dec	Q4	2003	1
12/31/2003	Dec	Q4	2003	1
12/31/2007	Dec	Q4	2007	1
12/29/2008	Dec	Q4	2008	1
12/30/2008	Dec	Q4	2008	1
12/31/2008	Dec	Q4	2008	1
12/31/2012	Dec	Q4	2012	1

Re: Odd behavior in Week function

Gysbert_Wassenaar — Mon, 17 Dec 2012 19:39:20 GMT

Week 1 is the first week with 4 or more days in the new year. This is an international convention (if maybe even a standard). Consequently, the weeks with 4, 5 or 6 days in the new week will have days in the old year. That's what you're seeing.

Re: Odd behavior in Week function

Mon, 17 Dec 2012 20:05:48 GMT

You're quite right about it being a standard. After reading your reply, I did a little digging and found that ISO-8601 states that the first week of the year is the one that contains the first Thursday. This is the definitive rule set:

Dates in January							Effect
M	T	W	T	F	S	S	Week number	Week assigned to
1	2	3	4	5	6	7	1	New year
	1	2	3	4	5	6	1	New year
		1	2	3	4	5	1	New year
			1	2	3	4	1	New year
				1	2	3	52 or 53	Previous year
					1	2	52 or 53	Previous year
						1	52 or 53	Previous year