Error in Master Calendar.

[Anonymous]

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

[Anonymous]

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

Cheeers,

Maanadh

[adj29block · Accepted solution]

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

[Anonymous]

Merci Jean-Baptiste.

It worked correctly. Can you just explain the code changes you made to the one i posted or is it the one you use by default.

Vous etes francais ? Je suis Indien. Je parle Francais aussi mais petit.

Thanks again !

[adj29block]

Hi

Here is the link that will explain the development of this Master Calendar. There are many others but I think it is the simplest for simple applications. He is in French
I do not use flags.
The difference with your script comes from the definition of Min and Max date with the Makedate () function and the While loop in ChampDate:
Be careful to format the date (OrderDate or Date or DateVentes or other) in Excel and with the function Makedate ()

https://community.qlik.com/docs/DOC-4848

Bonne journée.

[adj29block]

Hi

At the end of the text there is : Voila we created our Calendar. But to do things properly, we will delete the "ChampDate" table and the vMinDate and vMaxDate variables that serve us more.

DROP TABLE FieldDate; OK

Be Careful

No use :
SET vMinDate =;
SET vMaxDate =;

You would lose vMinDate and vMaxDate

J-B