The Week Function.

[#1]

Hello,

Please find attached an example where i have used the week function. Here it associates the 1st of January 2010 to the 53rd week.

Any comments why it should be so?

Thanks,

Ankur

[amien · Accepted solution]

Hi,

i had the same issue .. the problem is that the first of januari 2010 was in the last week of 2009. So .. ISO-wise .. this is correct.

the solution is to alter/manipulate the week number.

IF(Week (Date) = 53,1,Week (Date))

luckily week 53 doesnt exist in 2010..

[amien]

btw... ofcourse when adding more years .. add the year before the week

2010-53 --> 2010-01

[Oleg_Troyansky]

The rules of assigning the weeks are different in different places... I believe, QlikView week belongs to the year in which it begins. Since week 53 begins in the "previous" year, Jan 1 of "this year" will still belong to that week.

[#5]

Thank You, Amien & Oleg for your reply.

[#6]

Hello,
I had also the problem showing the weeks of the first quarter 2010 in a Listbox from left to right.
I solved it by sorting not mumeric but by "Lade-Reihenfolge" (german -> means something like "by loading sequence", last checkbox - see properties.)
Perhaps it helps too.

Greetings dj_skbs

[#7]

Hi DJ,

Thank you for your reply, but in my case it was a SIB with two months data and the week list box showed Week 1 to 8 and then 53 !!!!

Regards,

Ankur

[tanelry]

I believe QlikView uses ISO standard for week numbering (see http://en.wikipedia.org/wiki/Seven-day_week#Week_numbering). Hence the first or last week may spread into two years.
Use "weekyear" function to find year number that corresponds to a week number.
For best clarity I always use year and week number joint together, like this:

weekyear(Date) & '_' & text(num(week(Date),'00')) as Year_Week

[johnw]

Yes, QlikView uses the ISO standard. There are other standards. If you want a complete solution, you're going to have to tell us what YOUR standard is. In any case, attached are sample formulas and demonstrated results for some of the standards that I've run across.

[#10]

Hello John & Tanelry,

Thank you for your reply. Thanks for the example John, its very helpful.

Regards,

Ankur