Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

TempCalendar:

LOAD

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

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

MasterCalendar:

LOAD

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

RESIDENT

TempCalendar

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:

TempCalendar:
LOAD
DATE( + ROWNO() -1) AS TempDate
AUTOGENERATE  -  +1

1 Solution

Accepted Solutions
adj29block
Contributor III
Contributor III

Hi,

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

ChampDate:

LOAD

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

    AUTOGENERATE (1)

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

Calendrier:

LOAD

    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)*/

/*CurrentDate*/

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

/*PreviousDate*/

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

View solution in original post

14 Replies
OmarBenSalem

when you change


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


to AUTOGENERATE varMaxDate - varMinDate +1;


what happens?

Chanty4u
MVP
MVP

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

shraddha_g
Partner - Master III
Partner - Master III

Check what values you are getting for varMaxDate and varMinDate

teiswamsler
Partner - Creator III
Partner - Creator III

hi Naik

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

/Teis

calexandru
Partner - Contributor III
Partner - Contributor III

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.

Regards,

Alex

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

calexandru
Partner - Contributor III
Partner - Contributor III

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?

Cheers,

Alex