Hi,
I have the below script for the Master Calender, However, id like to add financial periods. Ive gotten it right to identify the Quarters correctly for the first 3 by adding Rowno()+2 , as my Financial Period starts in March. if i select Q4, only december is selected, it needs to select the next year's jan and feb aswell. Can anyone assist?
My code below:
QuartersMap:
MAPPING LOAD
RowNo()+2 as Month, //have added +2 to enable it to start at march
'Q' & Ceil(Rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
LOAD
MIN(TRANSACTION_DATE) as MinDate,
MAX(TRANSACTION_DATE) as MaxDate
Resident TRANSACTIONS;
LET varMinDate = num(peek('MinDate',0,'Temp'));
LET varMaxDate = num(peek('MaxDate',0,'Temp'));
DROP Table Temp;
TempCalender:
LOAD
$(varMinDate) + IterNo()-1 as Num,
Date ($(varMinDate)) + IterNo()-1 as TempDate
AutoGenerate 1 while $(varMinDate) + IterNo()-1 <= $(varMaxDate);
MasterCalender:
LOAD
TempDate as TRANSACTION_DATE,
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
YearToDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
ApplyMap('QuartersMap',month(TempDate), Null()) as Quarter,
Week(WeekStart(TempDate)) & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalender
Order by TempDate ASC;
Drop Table TempCalender;
Please can anyone assist?
Thanks in advance.
