Hi,
If you want to create the Master calendar with all dates in the years. then you can use the below script,
Transactions:
LOAD * INLINE [
%TransDate, TransType, TransValue
2012-01-12, Sale, 100
2012-02-01, Sale, 100
2012-03-05, Sale, 100
2012-03-31, Payment, 200
2012-04-29, Sale, 150
2012-05-19, Sale, 175
2012-06-02, Sale, 200
2012-06-30, Payment, 400
2012-07-03, Sale, 200
2012-08-13, Sale, 100
2012-09-22, Sale, 200
2012-09-30, Payment, 400
2012-10-10, Sale, 175
2012-11-14, Sale, 225
2012-12-02, Sale, 325
2012-12-31, Payment, 400
];
Max_Min_Dates:
Load
Max(Date#(%TransDate,'YYYY-MM-DD')) AS MaxDate,
Min(Date#(%TransDate,'YYYY-MM-DD')) AS MinDate
Resident Transactions;
Let VarMinDate =Num(YearStart(Peek('MinDate',0,'Max_Min_Dates')));
Let VarMaxDate =Num(YearEnd(Peek('MaxDate',0,'Max_Min_Dates')));
Drop Table Max_Min_Dates;
Temp_Calendar:
Load
Date($(VarMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(VarMinDate) + IterNo() -1 <= $(VarMaxDate);
Master_Calendar:
Load
TempDate AS [%TransDate],
Month(TempDate) AS [CalendarMonth],
Year(TempDate) AS [CalendarYear],
MonthName(TempDate) AS [CalendarYearMonth],
Week(TempDate) AS [WeekNo]
Resident Temp_Calendar
Order by TempDate;
Drop Table Temp_Calendar;
