6 Replies Latest reply: Nov 1, 2013 10:57 AM by Jon Einebærolm

# Mastercalendar

Hi,

Any one who knows how to add "tertials" (grouping of four and four month per calendar year) into this master calendar?

rowno() as Month,

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

AUTOGENERATE (12);

Temp:

min(opening_date) as minDate,

max(opening_date) as maxDate

Resident Medlemsutvikling;

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

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

DROP Table Temp;

TempCalendar:

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

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

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

MasterCalendar_medlemsutvikling:

TempDate AS opening_date,

week(TempDate) As Week2,

Year(TempDate) As Year2,

Month(TempDate) As Month2,

Day(TempDate) As Day2,

YeartoDate(TempDate)*-1 as CurYTDFlag2,

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

inyear(TempDate, Monthstart(\$(varMaxDate)),-1) as RC12_2,

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

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

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

WeekDay(TempDate) as WeekDay2,

if(Year(TempDate)=Year(Today()), 1) as Thisyear2,

if(Year(TempDate)=Year(Today())-1, 1) as Lastyear2

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

• ###### Re: Mastercalendar

Create the field in MasterCalendar_medlemsutvikling as 'T' & Ceil (Month(TempDate)/4) as Tertial

• ###### Re: Mastercalendar

Already tried that. Returning the following error, thanks anyway.

• ###### Re: Mastercalendar

I don't know what you tried, but my statement doesn't create or use any mapping.

• ###### Re: Mastercalendar

I misunderstood your solution, seams like your solution works as well, thanks!

• ###### Re: Mastercalendar

Hello Jon,

You could use the same approach you did for the Quarters and create a second mapping load:

map_Tertial:

Mapping LOAD RowNo() as Month, 'T' & Ceil (RowNo()/4) as Tertial
AutoGenerate (12);

Then, in the second part:

ApplyMap('map_Tertial', Month(TempDate), Null()) as Tertial2,

Cheers,

Philippe

• ###### Re: Mastercalendar

Works perfectly!! Thanks