T1:
LOAD
[Product Sale],
Date(Bill_Date) AS billdate
FROM
[excel data.xlsx]
(ooxml, embedded labels, table is Sheet1);
Temp:
Load
Min(billdate) as MinDate,
Max(billdate) as MaxDate
Resident T1;
SET vFiscalYearStartMonth = 4;
LET vStartDate = num(Peek('MinDate',0,'Temp'));
LET vEndDate = num(Peek('MaxDate',0,'Temp'));
DROP TABLE Temp;
FiscalCalendar:
LOAD
*,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
Dual(Text(Date(MonthEnd(billdate), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name
LOAD
*,
Year(billdate) AS Year, // Standard Calendar Year
Month(billdate) AS Month, // Standard Calendar Month
Date(MonthEnd(billdate), 'MMM') AS MonthName, // Standard Calendar Month Name
Dual('Q' & Ceil(Month(billdate)/3), Ceil(Month(billdate)/3)) AS Quarter, // Standard Calendar Quarter
Mod(Month(billdate) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month
YearName(billdate, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS billdate,
RangeSum(Peek('RowNum'), 1) AS RowNum
