14 Replies Latest reply: Dec 7, 2017 4:50 AM by Jean-Baptiste COCHOIS RSS

    Error in Master Calendar.

    Maanadh Naik

      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,

      DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS MonthYear,

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

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

      WEEKDAY(TempDate) AS WeekDay



      Order BY

      TempDate ASC;

      DROP FIELD TempDate;


      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
      AUTOGENERATE  -  +1

        • Re: Error in Master Calendar.
          omar bensalem

          when you change

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

          to AUTOGENERATE varMaxDate - varMinDate +1;

          what happens?

          • Re: Error in Master Calendar.
            Chanty 4u

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

            • Re: Error in Master Calendar.
              Shraddha Gajare

              Check what values you are getting for varMaxDate and varMinDate

              • Re: Error in Master Calendar.
                Teis thrane Wamsler

                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.
                  Alexandru Caprarescu

                  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.




                    • Re: Error in Master Calendar.
                      Maanadh Naik

                      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.

                        • Re: Error in Master Calendar.
                          Alexandru Caprarescu

                          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?




                            • Re: Error in Master Calendar.
                              Maanadh Naik

                              Thanks a lot Alex. Yes, I checked the date value is NULL for vMaxDate and vMinDate. I tried using the code in the Link. Didn't work. My colleague however did the exact same process and got the desired result. My workplace also doesn't allow me to watch the videos. Also, it is a bit tough to understand all the concepts within a day or so on my own. So still figuring things out




                        • Re: Error in Master Calendar.
                          Jean-Baptiste COCHOIS



                          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

                              AUTOGENERATE (1)

                              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]


                          RESIDENT ChampDate;

                          DROP TABLE ChampDate;


                          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