Issues With Master Calendar

[#1]

Hello All,

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

I have this script:

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

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:

Why is this? And how do I resolve it?

Thank you in advance,
Alison

[arvind_patil]

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

[#3]

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]

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;

[#5]

Hi Matteo,

Thank you, I am getting the following error however:

The following error occurred:

Field '<=' not found

The error occurred here:

?

[matteo901p]

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

[#7]

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

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]

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)

[#9]

Hi Matteo,

Sorry what do you mean? Do you mean this:

[matteo901p]

Can u attach .qvf of your app?