Custom month and year start day/date script - how?

Wed, 18 Apr 2012 14:46:36 GMT

Hi,

I've been wrestling with this for a while and finally figured out the custom week parts, but am still unsure how I can achieve the same thing for month and year values. Effectively as I understand it QC defaults to a Mon-Sun week calendar. I am trying to create a custom Sat-Fri production calendar. I have done this but now how do I create the same thing but for ProductionMonth and ProductionYear calendar. ProductionMonth ends on the last Friday of a CalendarMonth and the next month then starts on the Saturday - e.g. for 2012 Production-Jan would end on 27-Calendar-Jan and Production-Feb would start on 28-Calendar-Jan. ProductionYear ends on the last Fri of Dec, so for Production-2012 that would be 28-Calendar-Dec, and Production-2013 would start on 29-Calendar-Dec-Calendar 2012.

I was able to achive the result I wanted (ProdCalendarWeek) in the script using the WeekName function with weekoffset modifier. However, I cannot do this with similar Month and Year functions so am stuck.

The test QV code I'm working with is this,

Calendar:
LET vDateMin = Num(MakeDate(2011,1,1));
LET vDateMax = Num(MakeDate(2012,12,31)); 
LET vDateToday = Num(Today());
TempCalendar:
LOAD
$(vDateMin) + RowNo() - 1 AS DateNumber,
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar: //Custom production week is Sat-Fri period, month ends on the last prodn Friday and year ends on the last prodn Friday of Dec.
LOAD
Date(TempDate) AS CalendarDate,
WeekDay(TempDate) as CalendarDofW,
Week(TempDate) AS CalendarWeek,
Right(WeekName(TempDate,0,-2),2) as ProdCalendarWeek,
Month(TempDate) AS CalendarMonth,
//MonthName(TempDate,0) as ProdCalendarMonth,
Year(TempDate) AS CalendarYear,
//YearName(TempDate,0) as ProdCalendarYear,
WeekStart(TempDate,0,-2) as ProdWeekStart,
WeekEnd(TempDate,0,-2) as ProdWeekEnd
RESIDENT TempCalendar ORDER BY TempDate ASC;
DROP TABLE TempCalendar;

How can I adapt the script to provide a correct (as I defined in my 1st paragraph) value for ProdCalendarMonth and ProdCalendarYear?

In my example screenshot if it worked I'd expect the ffg result: (I've abbreviated Calendar to Cal below). I'm using date format DD/MM/YYYY.

The newly calculated fields I've called ProdCalMonth and ProdCalYear below and I contrast their output with that of the default QV Mon-Sun date functions.

CalDate CalWeek ProdCalWeek CalMonth ProdCalMonth CalYear ProdCalYear CalDayofWeek

01/01/2011 52 01 Jan Dec 2011 2010 Sat

02/01/2011 52 01 Jan Dec 2011 2010 Sun

03/01/2011 1 01 Jan Jan 2011 2011 Mon

04/01/2011 1 01 Jan Jan 2011 2011 Tue

05/01/2011 1 01 Jan Jan 2011 2011 Wed

06/01/2011 1 01 Jan Jan 2011 2011 Thu

07/01/2011 1 01 Jan Jan 2011 2011 Fri

08/01/2011 1 02 Jan Jan 2011 2011 Sat

09/01/2011 1 02 Jan Jan 2011 2011 Sun

10/01/2011 2 02 Jan Jan 2011 2011 Mon

... etc.

Thanks

topic Custom month and year start day/date script - how? in QlikView

Custom month and year start day/date script - how?