topic Re: Storing Date Formatted as YearWeek in QlikView

Storing Date Formatted as YearWeek

Mon, 30 Jun 2014 22:33:54 GMT

Hello,

I have a date in the format YearWeek (e.g. 201214 corresponds to the 14th week in the year 2012). Is there anyway to store this in QlikView in a way that will allow me to use a Master Calander? Everything I've tried so far returns a date in a format similar to '245047' (year=2450 week=47 when it should be year=2012, week=14).

I have found similar posts that recommend using the weekname() function, but that seems to only work if the date is already formatted in a D/M/Y like format.

Thanks for any suggestions!

Re: Storing Date Formatted as YearWeek

Mon, 30 Jun 2014 23:47:33 GMT

I don't think there is a way to store it in the master calendar as this does not correspond to a VALID date.

Re: Storing Date Formatted as YearWeek

Tue, 01 Jul 2014 00:43:24 GMT

I think what you need is a Week Starting or Week Ending Date along side your YearWeek field.

You can do this by building a calendar from scratch with Date, YearWeek and weeks starting/ending date (using WeekStart() or WeekEnd() function).

You then Left Join the generated YearWeek and Week Starting/Ending Date to your your existing data. Make sure to load distinct values for YearWeek and Week Starting/Ending Date.

Re: Storing Date Formatted as YearWeek

hic — Tue, 01 Jul 2014 08:48:36 GMT

Yes, that is possible. Try

MakeWeekDate(Left(YearWeek,4),Mid(YearWeek,5,2)) as Date,

HIC