topic Re: week returns wrong value in QlikView

week returns wrong value

Wed, 12 Sep 2012 11:06:10 GMT

Why does the function week return 53 for the date 01.01.2010????

regards,

Re: week returns wrong value

m_woolf — Wed, 12 Sep 2012 11:15:09 GMT

1/1/2010 was on a Friday. I guess that Qlikview interprets that as being in the last week of 2009.

Re: week returns wrong value

swuehl — Wed, 12 Sep 2012 11:28:58 GMT

I believe QV uses ISO 8601 for its date and calendar functions:

http://en.wikipedia.org/wiki/ISO_8601

So week 53 is correct for that date. [Check also the weekyear(DATE) function.]

Regards,

Stefan

Re: week returns wrong value

hic — Wed, 12 Sep 2012 12:53:30 GMT

Stefan is correct.

Basically there are two parameters when calculating week numbers:

When does week 1 start? In the US week 1 always starts at Jan 1. But the ISO standard (used in Europe) says that week 1 is the first week containing 4 or more days. So, in Europe Jan 1st can belong to week 53.
On what weekday does the week start? In the US, the week starts on the Sunday. The ISO standard (which most of Europe uses) prescribes that the Week starts on Monday. And in the middle east, the week starts on Saturday.

Today, QlikView only supports the ISO standard for both these bullets (hard-coded in the product).

HIC

Re: week returns wrong value

Thu, 13 Sep 2012 07:17:31 GMT

hm...is there anyway i could make the 53 to a 52??? cause like that the chart i created is not really readable.

regards,

Re: week returns wrong value

Gysbert_Wassenaar — Thu, 13 Sep 2012 11:55:00 GMT

sure, if(week(SomeDate)=53,52,week(SomeDate))

Re: week returns wrong value

rlp — Thu, 13 Sep 2012 14:45:05 GMT

You should use

week( Date ) -1

in order to differenciate the two consecutive last weeks.

Re: week returns wrong value

hic — Thu, 13 Sep 2012 14:49:11 GMT

If you want to redefine the Week numbers altogether, you can use

Div(date-YearStart(date)+WeekDay(YearStart(date))+7,7) as WeekNumber

This will always return week 1 for Jan 1st. It will use Monday as the first day of the week. If you want Sunday as the first day of the week, you should use

Div(date-YearStart(date) + WeekDay(YearStart(date)+1) +7,7) as WeekNumber

HIC

Re: week returns wrong value

Tue, 07 Jan 2014 18:39:50 GMT

Hi Henric- I have the opposite problem, it is interpreting 12/30/2013 & 12/31/2013 as week 1.

And I need them to be week 53.

Is it possible to correct this in the script?

Re: week returns wrong value

hic — Tue, 07 Jan 2014 20:19:26 GMT

The above formula

Div(date-YearStart(date) + WeekDay(YearStart(date)+1) +7,7) as WeekNumber

will return 53 for 12/30/3013.

Which type of week number do you want? I assume that it is the type "6/1/1" in the chart below (Week numbers are numbers with colored background.). If so you can use the above formula.

HIC