TEMP:
LOAD Max("Posting Date") AS MaxDate 
from [lib://3_Qvds/Fact.qvd] (qvd);
LET varMaxDate = Num(peek('MaxDate', 0, 'TEMP'));


TEMP1:
LOAD Min("Posting Date") AS MinDate
from [lib://3_Qvds/Fact.qvd](qvd);

LET varMinDate = Num(peek('MinDate', 0, 'TEMP1'));


//*************** Temporary Calendar ***************
TempCalendar:
LOAD
 $(varMinDate)+IterNo()-1 AS Num,
 Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************

MasterCalendar:
LOAD TempDate AS [Posting Date],
     TempDate,
	 TempDate AS %Date,
     YearName(TempDate,0,4) AS FinancialYear,
	 Date(Yearstart(TempDate,0,4)) AS YearStart,
	 Date(Yearend(TempDate,0,4)) AS YearEnd,
	 Num(Date(Yearstart(TempDate,0,4),'YYYY')) AS FinancialYearNo,
	 date(MonthStart(TempDate),'MMM-YY') AS MonthYear,
	 Month(TempDate) AS MonthName,
	 If(Num(Month(TempDate))>3,Num(Month(TempDate))-3,Num(Month(TempDate))+9) AS MonthNumber,
	 MonthStart(TempDate) AS MonthStart,
	 MonthEnd(TempDate) AS MonthEnd,
	 Year(TempDate) AS Year,
	 'Q' & alt(if(Month(TempDate)<4,4),if(Month(TempDate)<7,1),if(Month(TempDate)<10,2),3) AS Quarter,
	 quarterstart(TempDate,0,4) AS QuarterStart,
	 quarterend(TempDate,0,4) AS QuarterEnd,
	 quartername(TempDate,0,4) AS QuarterName,
	 if(Num(Month(TempDate))>3,Year(TempDate)+1,Year(TempDate)) AS FiscalYear,
     if(Num(Month(TempDate))>3,Year(TempDate)+1,Year(TempDate))-1 as P_FiscalYear ,
	 AutoNumber(Month(TempDate) & Year(TempDate)) AS Sequential,
	 NUM(TempDate) AS DateNum,
     monthname(TempDate) AS CalMonthYear,
	 Day(TempDate) &'-'& Date(TempDate,'MMM') AS DayMon,
  	 num(Year(TempDate)&num(Month(TempDate),'00')) as YEAR_MONTH, // Link between tables for previous month / year selection
     num((Year(TempDate)-1)&num(Month(TempDate),'00')) AS YEAR_MONTH_PREVIOUS, // Link between tables for previous month / year selection
     num((Year(TempDate)-2)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS2,
     num((Year(TempDate)-3)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS3,
     num((Year(TempDate)-4)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS4,
     num((Year(TempDate)-5)&num(Month(TempDate),'00')) AS P_YEAR_MONTH_PREVIOUS5,
     Date(TempDate,'MMM') &'-'& Date(TempDate,'YY') as YearMonth, 
     //quarterstart(TempDate,0,4) &'-'& Date(TempDate,'YY') as YearQtr
     'Q' & alt(if(Month(TempDate)<4,4),if(Month(TempDate)<7,1),if(Month(TempDate)<10,2),3) &'-'&right(Date(TempDate,'YY'),2) as  YearQtr,
     'FY' & YearName(TempDate,0,4) as ChartFinYear
    RESIDENT TempCalendar  
ORDER BY TempDate Asc;

DROP Table TempCalendar;
DROP Table TEMP;
DROP Table TEMP1;