The Master Calendar below seems to work fairly well, but I need to add a Fiscal period which is a 3 month offset from the calendar month (Fiscal Period 201301 = Calendar month 201304 (April)) also our Quarters are also offset Q1 = April thru June. How would I modify the script?
let vMinDate = num(makedate(2006,1,1));
let vMaxDate = num(makedate(2013,12,31));
Let vCurDate = Num(Today());
let vToday = $(vCurDate);
TempCal:
LOAD
date($(vMinDate) + RowNo() - 1 ) as TempDate
AutoGenerate
$(vMaxDate)-$(vMinDate) + 1;
MasterCalendar:
LOAD
TempDate as Date,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
WeekDay(TempDate) as WeekDay,
'Q' & Ceil(month(TempDate)/3) as Quarter,
Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,
Week(TempDate)& '-' & year(TempDate) as WeekYear,
InYearToDate(TempDate, $(vToday), 0) * -1 as CurYTDFlag,
InYearToDate(TempDate, $(vToday), -1) * -1 as LastYTDFlag
Resident TempCal
Order By TempDate asc;
DROP Table TempCal;
