Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
//Create min and max date (max=current date for the assignment)
Temp:
load date(minDate,'YYYY-MM-DD') AS minDate, date(maxDate,'YYYY-MM-DD') AS maxDate inline [
minDate, maxDate
'2005-12-30', '2009-06-30'
];
SET vFiscalYearStartMonth = 7;
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
*,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
Dual(Text(Date(MonthEnd(Key_OrderDate), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name
Load
//Master Calendar will Join its fields with Key_OrderDate field From our DB
TempDate AS Key_OrderDate,
week(TempDate) As OrderWeek,
Year(TempDate) As OrderYear,
Month(TempDate) As OrderMonth,
Day(TempDate) As OrderDay,
YeartoDate(TempDate)*-1 as OrderCurYTDFlag,
YeartoDate(TempDate,-1)*-1 as OrderLastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as OrderRC12,
date(monthstart(TempDate), 'MMM-YYYY') as OrderMonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as OrderQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as OrderWeekYear,
WeekDay(TempDate) as OrderWeekDa,
Mod(Month(TempDate) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month
YearName(TempDate, 0, $(vFiscalYearStartMonth)) AS FiscalYear // Fiscal Calendar Year
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.