topic Re: Issues With Master Calendar in App Development

Issues With Master Calendar

Fri, 28 Jul 2017 09:22:39 GMT

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

Re: Issues With Master Calendar

arvind_patil — Fri, 28 Jul 2017 11:58:42 GMT

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

Re: Issues With Master Calendar

Fri, 28 Jul 2017 12:08:01 GMT

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

Re: Issues With Master Calendar

matteo901p — Fri, 28 Jul 2017 13:42:44 GMT

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;

Re: Issues With Master Calendar

Fri, 28 Jul 2017 15:33:54 GMT

Hi Matteo,

Thank you, I am getting the following error however:

The following error occurred:

Field '<=' not found

The error occurred here:

Re: Issues With Master Calendar

matteo901p — Mon, 31 Jul 2017 09:46:47 GMT

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

Re: Issues With Master Calendar

Mon, 31 Jul 2017 10:40:44 GMT

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?

Re: Issues With Master Calendar

matteo901p — Mon, 31 Jul 2017 10:52:23 GMT

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)

Re: Issues With Master Calendar

Mon, 31 Jul 2017 10:55:14 GMT

Hi Matteo,

Sorry what do you mean? Do you mean this:

Re: Issues With Master Calendar

matteo901p — Mon, 31 Jul 2017 13:44:32 GMT

Can u attach .qvf of your app?