Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Users, make your voice heard! Participate in Wisdom of Crowds® Market Study: Start Survey
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Issues With Master Calendar

Hello All,

I am hoping you can help me with the present issue.

I have this script:

Screenshot (56).png

And this is the script I am using for the master calendar:

Screenshot (57).png

I get this error:

Invalid Autogenerate count: -1

The error occurred here:

MasterCalendar: LOAD Distinct text(num(MonthStart(Date( + RecNo() - 1)))) AS [Date], Year(Date( + RecNo() - 1)) as [Year], Month(Date( + RecNo() - 1)) as [Month], ceil(month(Date( + RecNo() - 1))/3) as Quarter, Date(monthStart(Date( + RecNo() - 1)), 'MM-YYYY') AS [MonthYear] AUTOGENERATE ( - +1)

And this is the result I get in the UI:

Screenshot (58).png

Why is this? And how do I resolve it?

Thank you in advance,
Alison

9 Replies
arvind_patil
Partner
Partner

HI Alison,

Please refer below master Calender:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

 

Temp: 

Load 

               min(VALIDDATE) as minDate,  //Your Date Field

               max(VALIDDATE) as maxDate  //Your Date Field

Resident Facts; 

 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

 

MasterCalendar: 

Load 

               TempDate AS VALIDDATE, 

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

Thanks,

Arvind Patil

Not applicable

Hello Arvind,

Thank you for your response. But, I am getting this error:

The following error occurred:

Table 'Facts' not found

The error occurred here:

Temp: Load min(Date) as minDate, max(Date) as maxDate Resident Facts

matteo901p
Partner
Partner

In Temp table you must calculate min & max of datefield of your fact table (resident it)

In your case..
Temp:

Load

               min([Merchandise Date])  as minDate,  //min date of Merch.

               max([Merchandise Date]) as maxDate  //max date of Merch.

Resident [Merchanise Income];

//Save min/max in variables from Temp Table and drop it

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

//Generate Calendar from minDate to MaxDate

TempCalendar:

LOAD

               $(varMinDate) + Iterno()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

//Create Full Calendar field and drop sourcetable TempCalendar

MasterCalendar:

Load

               TempDate AS VALIDDATE,

               week(TempDate) As Week,

               Year(TempDate) As Year,

               Month(TempDate) As Month,

               Day(TempDate) As Day,

               YeartoDate(TempDate)*-1 as CurYTDFlag,

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

               WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Not applicable

Hi Matteo,

Thank you, I am getting the following error however:

The following error occurred:

Field '<=' not found

The error occurred here:

?

matteo901p
Partner
Partner

1) Which format has your Date field from Merchandise Sheet (source file)?
2) Can you debug (or exit) before create TempCalendar in the script?

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;


ADD THIS

EXIT SCRIPT;

and check values of variables: varMinDate varMaxDate


I think that  these are not create correctly in the script.


PS: U can test it after reload -> Variable Section (in presentation, botton left).

Not applicable

I seem to have been able to get rid of the autogenerate error. Now my problem is different.

Screenshot (59).png

As you can see, three dates are being generated: 42917, 42934 and 42935. This is strange as in the data source, there are only two dates; 42935 and 42934. Where is 42917 coming from?

matteo901p
Partner
Partner

Can u post the final table that generate the MasterCalendar?
I think that 42917 is 01/07/2017 in Date Format (Monthstart of 42934-5)

Not applicable

Hi Matteo,

Sorry what do you mean? Do you mean this:

Screenshot (61).png

matteo901p
Partner
Partner

Can u attach .qvf of your app?