Fiscal Calender Genration Issue

Karim_Khan — Tue, 11 Aug 2015 14:47:08 GMT

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;

Re: Fiscal Calender Genration Issue

Anonymous — Tue, 11 Aug 2015 14:54:49 GMT

resident load is required for

LOAD

Min(Date) AS MinDate,

Max(Date) AS MaxDate

Resident Cisco;

Re: Fiscal Calender Genration Issue

maxgro — Tue, 11 Aug 2015 14:56:40 GMT

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;

Re: Fiscal Calender Genration Issue

Karim_Khan — Tue, 11 Aug 2015 15:48:15 GMT

Thank you so much

Re: Fiscal Calender Genration Issue

qlikviewwizard — Wed, 12 Aug 2015 06:21:20 GMT

Hi KarimKhan

As you got the answer, please close the thread by selecting Correct Answer. Thank you.

topic Fiscal Calender Genration Issue in QlikView

Fiscal Calender Genration Issue

Re: Fiscal Calender Genration Issue

Re: Fiscal Calender Genration Issue

Re: Fiscal Calender Genration Issue

Re: Fiscal Calender Genration Issue