Error message on Calendar script

[skzwane01]

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

[dplr-rn]

debug and check what the value of these variables are

vEndDate , vStartDate

share the actual script so we can help further

[Vegar]

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
;

[skzwane01]

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;

[skzwane01]

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;

[Vegar · Accepted solution]

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.

[dplr-rn]

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')));

[skzwane01]

Thank you it works 🙂