topic Error message on Calendar script in QlikView

Error message on Calendar script

skzwane01 — Wed, 25 Nov 2020 16:16:04 GMT

Good day I have calendar code below running successfully on Qlikview however when I take the same code and paste it on QlikSense it gives me the error message below. Any idea what is causing the error message ? The following error occurred: Autogenerate: generate count is out of range The error occurred here: LOAD Date( + RangeSum(Peek('RowNum'), 1) - 1) AS Date, RangeSum(Peek('RowNum'), 1) AS RowNum AutoGenerate vEndDate - vStartDate + 1

Re: Error message on Calendar script

dplr-rn — Mon, 12 Aug 2019 19:50:12 GMT

debug and check what the value of these variables are

vEndDate , vStartDate

share the actual script so we can help further

Re: Error message on Calendar script

Vegar — Mon, 12 Aug 2019 19:52:37 GMT

There is most likely an issue with your variables.

How is vEndDate and vStartDate defined?

Depending on your scenario, it might help to dollar expand your variables.

LOAD Date( + RangeSum(Peek('RowNum'), 1) - 1) AS Date, RangeSum(Peek('RowNum'), 1) AS RowNum AutoGenerate $(vEndDate) - $(vStartDate) + 1 ;

Re: Error message on Calendar script

skzwane01 — Mon, 12 Aug 2019 19:56:54 GMT

Hi please copy below script.

Note the variables have a hardcoded value

=================================================

SET vFiscalYearStartMonth = 3;

LET vStartDate = Num(Date(Date#('13/01/2011')));

LET vEndDate = Num(Date(Date#('17/10/2012')));

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(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/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: Error message on Calendar script

skzwane01 — Mon, 12 Aug 2019 20:00:01 GMT

Please see below script

SET vFiscalYearStartMonth = 3;

LET vStartDate = Num(Date(Date#('13/01/2011')));

LET vEndDate = Num(Date(Date#('17/10/2012')));

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(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/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: Error message on Calendar script

Vegar — Mon, 12 Aug 2019 20:05:41 GMT

Try this.

SET vFiscalYearStartMonth = 3; LET vStartDate = Num(Date(Date#('13/01/2011','DD/MM/YYYY'))); LET vEndDate = Num(Date(Date#('17/10/2012','DD/MM/YYYY'))); FiscalCalendar: LOAD [...]; LOAD [...]; LOAD Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date, RangeSum(Peek('RowNum'), 1) AS RowNum AutoGenerate vEndDate - vStartDate + 1;

My guess is that you have another DateFormat than what you are assuming in your script. (DD/MM/YYYY). I adjusted the script to explicit set the DateFormat used in the declaration of your variables.

Re: Error message on Calendar script

dplr-rn — Mon, 12 Aug 2019 20:01:31 GMT

change your variable definition in script to

LET vStartDate = Num(Date(Date#('13/01/2011','DD/MM/YYYY')));

LET vEndDate = Num(Date(Date#('17/10/2012','DD/MM/YYYY')));

Re: Error message on Calendar script

skzwane01 — Mon, 12 Aug 2019 20:10:37 GMT

Thank you it works 🙂