Hi Andrey,
Thanks for your answer.
It working fine and i need some changes in this. when i am using this script in master calendar i am not getting the correct values. Please look in the below script and do the needful. Thanks in advance.
MasterCalendar:
LOAD
Min(OrderDate)as MinDate,
Max(OrderDate) as MaxDate
RESIDENT Orders;
LET vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');
LET vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');
DROP TABLE Temp_Calendar_Range;
LOAD
Date(AddMonths($(vMinDate), RecNo()-1), 'YYYYMM') as FiscalYrMht1,
Date(AddMonths(AddMonths($(vMinDate), RecNo()-1), IterNo()-3), 'YYYYMM') as YrMht1,
Month(AddMonths($(vMinDate), RecNo()-1)) as Mht1,
RecNo() as MthCode1
AutoGenerate 36
While IterNo()<4;
Load *,
AutoNumber(FiscalYear & Month& OrderDate, 'OrderDateId') as OrderId,
AutoNumber(FiscalYear & Quarter, 'QuarterID') as QtrCode,
AutoNumber(FiscalYear & Month, 'MonthID') as MthOrder,
Month(AddMonths(OrderDate,-2)) &' '& FiscalYear as FiscalMonth,
'FY'&Right(FiscalYear,2)& Quarter as FisQtr,
Month(AddMonths(OrderDate,-2)) as Mth,
Date(AddMonths(OrderDate,-2),'YYYYMM') as FiscalYrMth;
LOAD DISTINCT
Temp_Date as OrderDate,
Year(Temp_Date) as FiscalYear,
Month(Temp_Date) as [Month],
num(Month(Temp_Date)) as MthCode,
AutoNumber(year(Temp_Date)&(Month(Temp_Date))) as monthid,
Date(Temp_Date, 'YYYY-MM') as [Year - Month],
'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter];
LOAD DISTINCT
MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date
AUTOGENERATE (1)
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);
