Week() beyond 52 weeks?

[#1]

Hi again,

Is there a way to make week() go beyond a 1 year period? I need to display week numbers from a range of more than 3 years, so I need to make the week count go above 100. Anyone know an easy way to do this? Thanks.

[disqr_rm]

How about:

Get the start year stored in Start_Year variable, something like min(year(Date)) and then

LOAD week(Date) * (year(Date) - $(Start_Year) + 1) as BigWeekNumber

Just an idea.

[johnw · Accepted solution]

That won't work. Let's say you want to start counting weeks from Monday, December 31, 2007, which QlikView considers week 1 of 2008:

December 31, 2007 -> 1
January 1, 2008 -> 2
January 7, 2008 -> 4
December 28, 2008 -> 104
December 29, 2008 -> 2
January 1, 2009 -> 3
January 5, 2009 -> 6
December 31, 2009 -> 159
January 1, 2010 -> 212

I'd recommend this (replacing the date with whatever starting date you want):

1+floor((Date-date#(20071231,'YYYYMMDD'))/7)

[disqr_rm]

Sure, but on the other hand, I just gave an idea and not a 100% solution. I expect members to do some homework themselves.

This "week" thing is pretty tricky and discussion cen be little sensitive based on requirements. Remember our favorite thread http://community.qlik.com/forums/p/18218/71427.aspx#71427 ?

[johnw]

Eep! Don't remind me!