topic Mastercalendar in QlikView

Mastercalendar

Fri, 01 Nov 2013 14:27:29 GMT

Hi,

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

MAPPING LOAD

rowno() as Month,

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

AUTOGENERATE (12);

Temp:
Load

               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:
LOAD
               $(varMinDate) + Iterno()-1 As Num,

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

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

MasterCalendar_medlemsutvikling:
Load

               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

Gysbert_Wassenaar — Fri, 01 Nov 2013 14:31:11 GMT

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

Re: Mastercalendar

pgrenier — Fri, 01 Nov 2013 14:39:52 GMT

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

Fri, 01 Nov 2013 14:48:32 GMT

Works perfectly!! Thanks

Re: Mastercalendar

Fri, 01 Nov 2013 14:50:52 GMT

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

Re: Mastercalendar

Gysbert_Wassenaar — Fri, 01 Nov 2013 14:52:10 GMT

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

Re: Mastercalendar

Fri, 01 Nov 2013 14:57:05 GMT

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