Hello,
I'm using the following fiscal year load script to set the months and fiscal year and it works as intended. When the forecast date is not entered, I would like to group these records as "TBD" for the FY. How would I mod the script to do this?
QuartersMap:
MAPPING LOAD * Inline [
Month, Q
1, Q2
2, Q2
3, Q2
4, Q3
5, Q3
6, Q3
7, Q4
8, Q4
9, Q4
10, Q1
11, Q1
12, Q1
];
Temp:
Load
min("Forecasted Award Date") as minDate,
max("Forecasted Award Date") as maxDate
Resident Forecast;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS "Forecasted Award Date",
week(TempDate) As ForecastAwardWeek,
Year(TempDate) As ForecastAwardYear,
Month(TempDate) As ForecastAwardMonth,
Day(TempDate) As ForecastAwardDay,
YeartoDate(TempDate)*-1 as ForecastAwardCurYTDFlag,
YeartoDate(TempDate,-1)*-1 as ForecastAwardLastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as ForecastAwardRC12,
date(monthstart(TempDate), 'MMM-YYYY') as ForecastAwardMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as ForecastAwardQuarter,
If ((Month(TempDate) = 10 or Month(TempDate) = 11 or Month(TempDate) = 12), Year(TempDate) + 1, Year(TempDate)) as ForecastAwardFiscalYear,
Week(TempDate) & '-' & Year(TempDate) as ForecastAwardWeekYear,
WeekDay(TempDate) as ForecastAwardWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
