Hi Team,
I'm trying to Generate the Fiscal Calendar from my existing Data but am getting the below error.PFA
My Script is as below
Cisco:
LOAD [End Customer Global Ultimate Name],
Country,
[End Customer HQ Top],
[Sales Level 1],
[Sales Level 2],
[Sales Level 3],
[Sales Level 4],
[Sales Level 5],
[Partner Name],
[Fiscal Period ID],
Year(Date#([Fiscal Period ID],'YYYYMM')) AS Year,
Month(Date#([Fiscal Period ID],'YYYYMM')) AS Month,
Week(Date#([Fiscal Period ID],'YYYYMM')) AS Week,
Date(Num(Date#([Fiscal Period ID],'YYYYMM'),'DD-MM-YYYY')) AS Date,
FMV,
[Net Bookings],
Group
FROM
\\india.eclerx.com\ctrxdata\ARLM_R_DATA\Karim.Khan\Desktop\Sample_File.xlsx
(ooxml, embedded labels, table is Sheet1);
SET vFiscalYearStartMonth = 4;
DateRange:
LOAD
Min(Date) AS MinDate,
Max(Date) AS MaxDate
FROM Cisco;
LET vStartDate = Date(Peek(MinDate));
LET vEndDate = Date(Peek(MaxDate));
DROP TABLE DateRange;
FiscalCalendar:
LOAD
*,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
Dual(Text(Date(MonthEnd(Date), '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(Date), 'MMM') AS MonthName, // Standard Calendar Month Name
Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(billdate)/3)) AS Quarter, // Standard Calendar Quarter
Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month
YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;
KK
