topic ----------------- in QlikView

-----------------

Mon, 18 Mar 2013 12:32:52 GMT

-----------

Re: how does QV calculates the week

johanlindell — Mon, 18 Mar 2013 12:45:25 GMT

12/30 is week 52 (year 2012)

12/31 is week 1 (year 2012)

1/1 is week 1 (year 2013)

So far everything is OK. If you ant to make a year-week field, like 2012w52, you get into trouble. In this case you must make sure that 12/31 gets into the next year, i.e. it goes into week 2012w01.

You can do this "easily" by checking the day and week number to determine if the date belongs to a week in the next year or last year.

Re: how does QV calculates the week

Anonymous — Mon, 18 Mar 2013 13:02:17 GMT

Hi Good question,

Try using week function in your date coloumn you will get the answer,

like for example you have two dates say 31-12-2012 and 1-1-2013 then if you will apply week function

it will show week 1 in 2013 year which is correct as 31 dec and 1 jan was Mon and Tue of the week and therefore

it will show 31-12-2012 and 1 jan in week 1 and dates below 31-12-2012 in 52 week .

Regards,

anant

Re: how does QV calculates the week

hic — Mon, 18 Mar 2013 13:08:20 GMT

QlikView uses ISO 8601 to calculate week number (See http://en.wikipedia.org/wiki/ISO_week_date)

This means that the week starts on a Monday, and that week 1 is

the week with the year's first Thursday in it (the ISO 8601 definition)
the week with the Thursday in the period 1 – 7 January
the week starting with the Monday in the period 29 December – 4 January
the week starting with the Monday which is nearest in time to 1 January
the week ending with the Sunday in the period 4 – 10 January
the week with 4 January in it
the first week with the majority (four or more) of its days in the starting year
If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is part of week 52 or 53 of the previous year.
the week with the year's first working day in it (if Saturdays, Sundays, and 1 January are not working days).

HIC

Re: how does QV calculates the week

johanlindell — Mon, 18 Mar 2013 13:29:38 GMT

I think you need to get rid of the syntetic table to get it to work. There's seems to be something fishy in the links between the calendar and your data.

I think you have managed to somehow duplicate the date throught the syntetic keys. Creating a Table box with Link_Date, YearPeriod2 and Week shows up like this:

%Link_Date	YearPeriod2	Week
01/01/2011	201101	52
01/01/2011
01/02/2011	201101	52
01/02/2011
01/03/2011	201101	1
01/03/2011
01/04/2011	201101	1
01/04/2011
01/05/2011	201101	1
01/05/2011

Your sales is linked to the "rows" with no YearPeriod and no Weeks.

Re: how does QV calculates the week

rbecher — Mon, 18 Mar 2013 13:45:38 GMT

Btw. you cannot mix months and weeks in a chart because these are two different date hierarchies.

- Ralf

Re: how does QV calculates the week

struniger — Mon, 27 May 2013 14:36:21 GMT

Just ran into this issue as well. (2012.12.31 was hitting me..)

The solution was/is the function weekyear(date)

Here's the explanation from the help:

<quote>

The year to which the week number belongs. The week number ranges between 1 and approximately 52. Some years week #1 starts in December, e.g. December 1997. Other years start with week #53 of previous year, e.g. January 1999. For those few days when the week number belongs to another year, the functions year and weekyear will return different values.

</quote>

Since I want the format YYYY.WW I used the following, clumsy statement for my weekyear:

weekyear(D)&'.'&if(len(week(D))=1,'0'&week(D),week(D))

Works fine though...

Stefan

Re: how does QV calculates the week

hic — Mon, 27 May 2013 15:16:09 GMT

I would go for the following instead:

Dual(WeekYear(Date) &'.'& Num(Week(Date),'00'),WeekStart(Date))

Does the same as your expression but the dual puts a numeric value into the field also. Good for comparisons and sorting.

HIC