Hi
I have the following calendar:
LET vDateMin = Num(MakeDate(2018,10,1));
LET vDateMax = Floor(AddMonths(YearEnd(AddMonths(Today(),5)),100));
CalendarDates:
LOAD
$(vDateMin) + RowNo() - 1 as DateNumber
, Date($(vDateMin) + RowNo() - 1) as TempDate
, Date($(vDateToday)) as DateToday
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);
Calendar:
LOAD
TempDate as %Key_Date
, TempDate
, Num(Week(TempDate),00) as Calendar_Week_Number
, Month(TempDate) as Calendar_Month
, Num(Month(TempDate),00) as Calendar_Month_Number
, Year(TempDate) as Calendar_Year
, Year(TempDate) & 'Q' & Ceil(Month(TempDate)/3) as Calendar_Quarter
, WeekYear(TempDate) & 'W' & Num(Week(TempDate),00) as Calendar_Year_Week
, Year(TempDate) & '-' & Num(Month(TempDate),00) as Calendar_Year_Month
, Year(TempDate) & 'Q' & Ceil(Month(TempDate)/3) as Calendar_Year_Quarter
, Num(Month(AddMonths(TempDate,3)),00) as Fiscal_Month_Number
, Month(AddMonths(TempDate,3)) as Fiscal_Month
, Week(AddMonths(TempDate,3)) as Fiscal_Week
, weekyear(AddMonths(TempDate,3)) & 'WK' & Num(Week(AddMonths(TempDate,3)),00) as Fiscal_Year_Week
, Year(AddMonths(TempDate,3)) as Fiscal_Year
, Year(AddMonths(TempDate,3)) & 'Q' & Ceil(Month(AddMonths(TempDate,3))/3) as Fiscal_Quarter
, Year(DateToday) as Fiscal_Year_Today
It works great, except for this field:
weekyear(AddMonths(TempDate,3)) & 'WK' & Num(Week(AddMonths(TempDate,3)),00) as Fiscal_Year_Week
It almost gets the weeks number correctly, but the week starts on Wednesday.
Any ideas what I am doing wrong?
