Solved: Re: Error in Master Calendar. - Qlik Community

Anonymous · ‎2017-12-04

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

adj29block · ‎2017-12-06

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

OmarBenSalem · ‎2017-12-04

when you change

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

to AUTOGENERATE varMaxDate - varMinDate +1;

what happens?

Chanty4u · ‎2017-12-04

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

shraddha_g · ‎2017-12-04

Check what values you are getting for varMaxDate and varMinDate

teiswamsler · ‎2017-12-04

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 · ‎2017-12-04

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 · ‎2017-12-04

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

Anonymous · ‎2017-12-04

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 · ‎2017-12-04

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 · ‎2017-12-05

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