topic week mis match in QlikView

week mis match

Anonymous — Mon, 12 Jan 2015 11:10:37 GMT

Hi All,

I am having a date column with timestamp. And when I am trying to create Week for the chart it is getting mismatched.

Ex:

DATE:12/30/2014 8:12:00 PM

When I am trying to create the week with above DATE field ,it shows as 1 week. Any ideas?

When end of Dec how it can be a first week?

-Jai

Re: week mis match

Mon, 12 Jan 2015 11:31:55 GMT

Hi Ij

It is correct it should be 1, you may think its should b week 52, but this week overlaps with the first week of January 2015, as in week 52 of 2014 is combined with week 1 of 2015, therefore you will get 1. please check your calendar to verify this.

Hope this helps you.

Kind regards

Dlamini

Re: week mis match

Gysbert_Wassenaar — Mon, 12 Jan 2015 11:34:20 GMT

The week numbers are calculated using the ISO 8601 standard. The week number for 12/30/2014 is 1. It's correct. If you want to do it differently read this blog post: Redefining the Week Numbers

Re: week mis match

Mon, 12 Jan 2015 11:45:53 GMT

Hi IJ

There is a definition made by the International Organization for Standardization (ISO 8601) that QlikView uses to calculate week numbers. It states that

The week starts on a Monday.
A week is always unbroken, i.e. some years week 1 starts already in December, and in other years week 52 or 53 continues into January.
Week 1 always contains Jan 4th. Or, differently put: Week 1 always has at least 4 days in January. A third way to say it is: The first Thursday of the year always lies in week 1."

Regards

Dlamini

Re: week mis match

ankitaag — Mon, 12 Jan 2015 11:47:35 GMT

Hello Jai,

Try the below code in your script if you don't want 2014-12-30 as your 1st week.

LOAD Dates , Div(Dates-YearStart(Dates,0,1) + WeekDay(YearStart(Dates,0,1)) +7,7) as FiscalWeek

FROM

Dates.xlsx

(ooxml, embedded labels, table is Sheet1);

Thanks and Regards,

Ankita