Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
resident load is required for
LOAD
Min(Date) AS MinDate,
Max(Date) AS MaxDate
Resident Cisco;
this
DateRange:
LOAD
Min(Date) AS MinDate,
Max(Date) AS MaxDate
FROM Cisco;
should be
DateRange:
LOAD
Min(Date) AS MinDate,
Max(Date) AS MaxDate
Resident Cisco;
Thank you so much
Hi KarimKhan
As you got the answer, please close the thread by selecting Correct Answer. Thank you.