Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have create a master calendar using the data load editor. See below.
My question is how to calculate current YTD and prior YTD as master measure with below master calendar.
QuartersMap:
MAPPING LOAD
RowNo() as Month,
'Q' & Ceil (RowNo()/3) as Quarter
AutoGenerate(12);
Temp:
Load
min("Posting Date") as minDate,
max("Posting Date") as maxDate
Resident Entry;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, '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 "Posting Date",
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter,
Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay,
Left(Month(TempDate),3)&'-'&Right(Year(TempDate),2) as MonthYear
RESIDENT TempCalendar
ORDER BY TempDate ASC;
DROP TABLE TempCalendar;
DROP TABLE Temp;
Check this thread and modify field name as per your fieldname