Storing Date Formatted as YearWeek

[#1]

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!

[#2]

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

[#3]

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.

[hic · Accepted solution]

Yes, that is possible. Try

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

HIC