topic Week() beyond 52 weeks? in QlikView

Week() beyond 52 weeks?

Wed, 19 Aug 2009 21:22:51 GMT

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.

Week() beyond 52 weeks?

disqr_rm — Wed, 19 Aug 2009 21:30:35 GMT

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.

Week() beyond 52 weeks?

johnw — Thu, 20 Aug 2009 03:42:27 GMT

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)

Week() beyond 52 weeks?

disqr_rm — Thu, 20 Aug 2009 06:39:53 GMT

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 ?

Week() beyond 52 weeks?

johnw — Thu, 20 Aug 2009 06:54:38 GMT

Eep! Don't remind me!