Hi,
Here it is basic master calendar genaeration script
MinMax:
LOAD
Max([CreatedOn]) AS MaxDate,
Min([CreatedOn]) AS MinDate
RESIDENT YourDataTable;
LET varMinDate = Num(Peek('MinDate',0,'MinMax'));
LET varMaxDate = Num(Peek('MaxDate',-1,'MinMax'));
LET varToday = num(today());
// Now create the master calendar
MasterCalendar:
LOAD
*,
Date([CreatedOn]) AS [ Created Date],
Date([CreatedOn]) AS [Date],
Year([CreatedOn]) AS [Year],
'Q' & ceil(Month([CreatedOn])/3) AS [Quarter],
Month([CreatedOn]) AS [Calendar Month],
'FY' & Right(Year(AddMonths([CreatedOn],-6)),2) & '/' & Right(Year(AddMonths([CreatedOn],6)),2) AS [Financial Year],
// dual(text(Month([CreatedOn])),applymap('FiscalMonth_Map', text(Month([CreatedOn])))) AS [Financial Month],
// applymap('FinQuarter_map', Month([CreatedOn])) AS FinQtr,//orders the quarter to the Financial Year 1/7 30/6
// applymap('FiscalMonth_Map', Month([CreatedOn])) AS [Fin Month],//orders the quarter to the Financial Year 1/7 30/6
Week([CreatedOn]) AS Week,
Day([CreatedOn]) AS [Day],
Weekday([CreatedOn]) AS WeekDay,
Date(monthstart([CreatedOn]), 'MMM-YYYY') AS MonthYear,
If([Start Date]>addmonths(Today(), -12), 1) as Rolling12MonthsFlag,
Week([CreatedOn])&'-'&Year([CreatedOn]) AS WeekYear,
date(daystart(weekend([CreatedOn],0,5)),'DD/MM/YYYY') as [Week Ending],
inyeartodate([CreatedOn], $(varToday), 0) * -1 AS CurYTDFlag,
inyeartodate([CreatedOn], $(varToday), -1) * -1 AS LastYTDFlag,
InMonthToDate([CreatedOn], $(varToday), 0) * -1 as CurMTDFlag,
InMonthToDate([CreatedOn], $(varToday), -1) * -1 as LastMTDFlag;
LOAD
Date($(varMinDate) + RecNo() - 1) AS [CreatedOn]
AUTOGENERATE ($(varMaxDate) - $(varMinDate)+1);
DROP TABLE MinMax;
Note:-It is basic calendar created by MIN and MAX dates
Regards
Anand
