Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

Error in Master Calendar.

I am new to QlikSense. I was trying to create Master Calendar as per the guide titled "building an app", page 33 in the help section of help.qlik.

I replaced the the existing code in Main with

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

AND created a MasterCalendar at bottom of the list with the code

LET varMinDate = NUM(PEEK('Date',0,'Sales'));

LET varMaxDate = NUM(PEEK('Date',-1,'Sales'));



DATE($(varMinDate) + ROWNO() -1) AS TempDate

AUTOGENERATE $(varMaxDate) - $(varMinDate) +1;



DATE(TempDate) AS Date,

WEEK(TempDate) AS Week,

YEAR(TempDate) AS Year,

MONTH(TempDate) AS Month,

DAY(TempDate) AS Day,

INYEARTODATE(TempDate,'2014-03-31', 0) *-1 AS CYTDFlag,

INYEARTODATE(TempDate,'2013-03-31',0) *-1 AS LYTDFlag,


'Q' & CEIL(MONTH(TempDate)/3) AS Quarter,

WEEK(TempDate)&'-'&YEAR(TempDate) AS WeekYear,

WEEKDAY(TempDate) AS WeekDay



Order BY

TempDate ASC;


However, i get the foll error

The following error occurred:

while my colleague who did the exact same thing got no error.

Autogenerate: generate count is negative

The error occurred here:

DATE( + ROWNO() -1) AS TempDate

Tags (1)
1 Solution

Accepted Solutions
New Contributor III

Re: Error in Master Calendar.


I always use for all my applications the following MasterCalendar and not the one generated by Qlik Sense.

Italic comments.

I always place it after Hand

//The actual sales dates are from January 21st 2011 to September 29th 2014
// We extend the calendar to have a full year from 01 January 2011 to 31 December 2014 with Num(Makedate(Y,M,D))

LET vMinDate = Num(Makedate(2011,1,1));

LET vMaxDate = Num(Makedate(2014,12,31));



    date($(vMinDate)+IterNo()-1) as TempDate


    WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);



    TempDate as OrderDate,

    Year(TempDate) as Year,

    'Q ' & Num(Ceil(Month(TempDate)/3),'(ROM)0') as Quarter,

    Month(TempDate) as Month,

    Date(monthstart(TempDate), 'MMM YYYY') as MonthYear,

     Date(MonthStart(TempDate), 'YYYY MMM') as YearMonth,

//     Dual(Month(TempDate)& '-' & Year(TempDate),MonthStart(TempDate)) As MonthYear,

    MonthEnd(TempDate)as MonthEnd,

    MonthStart(TempDate) as MonthStart,

    Week(TempDate) as Week,

    Weekday(TempDate) as WeekDay,

    Day(TempDate) as Day,

//    Year Month SEQ sera utilisé dans le Rolling date Month-12

    AutoNumber(Num(Month(TempDate), '00')&'/'& Year(TempDate), 'Year Month SEQ') as [Year Month SEQ]



Variables HAND after Set :

Let vLastReload = Date(Now(), 'DD MMMM YYYY hh:mm');

Let vTodayDate =Today(Now()); /*Date Today (2017,12,6)*/


let vCYear  = '=Max(Year)';

let vCDate  = '=Max(OrderDate)';

Let vToday = '=vCDate'; /*vToday = vCDate = Max(OrderDate) = Max Date Table Sales, Field Date 26/06/2014*/

let vCMonth  = '=Month(Max(OrderDate))';

let vCDay   = '=Day(Max(OrderDate))';


let vPYear  = '=(Max(Year)-1)'; // '=vCYear-1';

let vPDate = '=Date(AddYears(Max(OrderDate),-1))';

let vPMonth  = '=Month(AddMonths(Max(OrderDate),-1))';

Let vPDay = '=Day(Max(OrderDate)-1)';

In Txt box

Variables Current Date :

=vCYear : 2014

=vCDate : 31/12/2014

=vCMonth : Dec.

=vCDay : 31

Idem PreviousDate

=vPYear : 2013

=vPDate : 31/12/2013

=vPMonth : Nov.

=VPDay : 30

For Rolling 12 Months with Max(OrderDate) but you can use Variable

Dimension : YearMonth

Expression :

sum( {<YearMonth=, OrderDate= {">=$(=MonthStart(AddMonths(Max(OrderDate), -11)))<=$(=MonthEnd(AddMonths(Max(OrderDate), 0)))"} >} Sales )

Rolling 3 Months :

sum( {<YearMonth=, OrderDate= {">=$(=MonthStart(AddMonths(Max(OrderDate), -2)))<=$(=MonthEnd(AddMonths(Max(OrderDate),0)))"} >} Margin )

Good Luck

14 Replies

Re: Error in Master Calendar.

when you change

AUTOGENERATE $(varMaxDate) - $(varMinDate) +1;

to AUTOGENERATE varMaxDate - varMinDate +1;

what happens?

Esteemed Contributor III

Re: Error in Master Calendar.

debug the code and check line by line which date is passing ?


Re: Error in Master Calendar.

Check what values you are getting for varMaxDate and varMinDate


Re: Error in Master Calendar.

hi Naik

If you use the data maneger to prep the data, Qlik Sense will automatic generate a usable calender base on derived fields



Re: Error in Master Calendar.

Hey there,

As a rule of thumb you want to make sure the following hold true:

The date field in your sales table was correctly parsed as date. You might want to check the tags associated with the field in the 'Data Model Viewer' and look for tags (you want to make sure you have $date or $timestamp there).

If this isn't the case then see what format your date comes in and either change the DateFormat variable or force date interpretation with Date#()

After you have the correct tags, use the debugger to see what the varMaxDate and varMinDate variables get assigned.

This can get messed up because your table or field aren't name exactly as in your peek variable.



New Contributor II

Re: Error in Master Calendar.

Thanks. I tried it but the result is still the same.

New Contributor II

Re: Error in Master Calendar.

Thanks Alex. It's just been my first day working on  QlikSense. So i am still trying to figure things out. I referred to the tutorial on qlik. It just asked me to copy-paste it for the time-being while it would be explained in the further tutorials. Could you suggest how can i debug ? it will be of great help.

New Contributor II

Re: Error in Master Calendar.

Hi Teis,Thank you for helping me out. Did you mean the default Set variables regarding time already present in MAIN ? The tutorial asked me to delete and replace with 10 SET lines as mentioned in my above query.


Re: Error in Master Calendar.

Hi there Maanadh,

I just noticed the reply, sorry for the delay.

First off, props on the huge leap, it's quite something for your first day working in Qlik Sense.

In regards to the debugger, you need to click the bug icon just left of the load button.

This brings up the debugging panel on the bottom and that has three sections.

Output, Variables and Breakpoints. You want to have at least the Variables one active (dark grey) so you can see what values get assigned to what variables.

To do a debug run you need to click the play button that sits on the top right side of the debugger panel.

You can disregard the error and just look at the variables and see what the vMaxDate and vMinDate hold, it should be a number or date. If it's a NULL, it's a problem.

I would suggest you follow this tutorial as well, and use the script provided as it's a bit more tidy and a bit more readable.

If you can let us know what exactly happens inside the variable I'm sure we can figure things out. Also maybe post a snippet of the code you use to load the Sales data?