Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Team,
my finance year start from Apr to Mar
data is coming like below format
01-04-15
02-05-15
02-06-15
03-07-15
03-08-15
03-09-15
04-10-15
04-11-15
05-12-15
05-01-16
05-02-16
07-03-16
for this i want to create year selections like 2015-2016, etc....?
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(OrderDate) as minDate,
max(OrderDate) as maxDate
Resident TableName;
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 FieldName,
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,
//inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
Date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
//Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;