Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

week returns wrong value

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

regards,

MT

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

9 Replies
m_woolf
Master II
Master II

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

swuehl
MVP
MVP

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

hic
Former Employee
Former Employee

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

Not applicable
Author

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

regards,

MT

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
rlp
Creator
Creator

You should use

week( Date ) -1

in order to differenciate the two consecutive last weeks.

hic
Former Employee
Former Employee

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

Not applicable
Author

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?

hic
Former Employee
Former Employee

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.

Newyear.png

HIC