You would normally have a fact table/transaction-like table with the range of dates in question but possibly with some holes regarding some or many dates. You would normally want your master calendar to start at the first date and end at the last date or a few months into the future - for forecasting or estimates for example.
MinMaxDates:
LOAD Min(SalesDate) AS minDate,Max(SalesDate) AS maxDate RESIDENT Sales;
vMinDate=Num(Peek('minDate',0,'MinMaxDates'));
vMaxDate=Num(Peek('maxDate',0,'MinMaxDates'));
vNumDates = vMaxDate-vMinDate+1; // inclusive the min and max
DROP TABLE MinMaxDates;
// Here comes two different approaches - #1 is based on a FOR loop and #2 on AUTOGENERATE n
FOR i=1 TO vNumDates
MasterCalendar1: // Much much slower than the next approach
LOAD
Date,
Year(Date) AS Year,
Ceil(Month(Date)/3) AS Quarter,
Month(Date) AS Month,
Day(Date) AS Day;
LOAD
Date( $(vMinDate) + $(i) - 1 ) AS Date
AUTOGENERATE 1;
NEXT
MasterCalendar2: // With LOAD iteration based on AUTOGENERATE and RowNo() - very quick.
LOAD
Date AS Date2,
Year(Date) AS Year2,
Ceil(Month(Date)/3) AS Quarter2,
Month(Date) AS Month2,
Day(Date) AS Day2;
LOAD
Date( $(vMinDate) + RowNo() - 1 ) AS Date
AUTOGENERATE $(vNumDates);
