My Calendar Fields are Missing values
My year list box shows:
2450
Week Listbox is missing
1
2
and above 45
Here is my script:
//-------------------------------------------------------------
//Get Dates from Data
//-------------------------------------------------------------
DateTemp:
LOAD YearMonth
RESIDENT Transactions
ORDER BY YearMonth Asc;
LET vMinDate = Num(Peek('YearMonth', 0, 'DateTemp')); //LET ... evaluates the expression on the right side of the ’=’ before it is assigned to the variable
LET vMaxDate = Num(Peek('YearMonth', -1, 'DateTemp'));
LET vToday = Num(today());
//-------------------------------------------------------------
//Temporary Calendar
//-------------------------------------------------------------
TempCalendar:
LOAD
$(vMinDate)+IterNo()-1 AS Num,
Date($(vMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);
//-------------------------------------------------------------
//Master Calendar
//-------------------------------------------------------------
MasterCalendar:
LEFT KEEP (Transactions)
LOAD TempDate AS %_DateKey,
TempDate AS %_DateKeyCA,
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,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,
num(month(TempDate)) as Period
RESIDENT TempCalendar
ORDER BY TempDate Asc;
Drop table TempCalendar;
Drop table DateTemp;
