topic Re: Master Calendar from start date and Enddate in QlikView

Master Calendar from start date and Enddate

rish_sai — Tue, 02 Aug 2016 15:22:30 GMT

Hi,

In my I have table which shows start date and End date columns ,

so how to build a calender as i have to consider all three date consider...

Re: Master Calendar from start date and Enddate

Anonymous — Tue, 02 Aug 2016 15:27:38 GMT

Do you mean you have three dates to consider for Master calendar ?

Re: Master Calendar from start date and Enddate

Anonymous — Tue, 02 Aug 2016 15:33:29 GMT

if it is for start date and End date columns , you can try below..

QuartersMap:

MAPPING LOAD

rowno() as Month,

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

AUTOGENERATE (12);

Temp:

Load

min([start date]) as minDate,

max([End date]) as maxDate

Resident Orders; //your table name in your data..............

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 OrderDate,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

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: Master Calendar from start date and Enddate

Anonymous — Tue, 02 Aug 2016 15:36:54 GMT

Hi Can I suggest having a read of Henric's blog on the canonical dates approach when you need to link several dates to a calendar.

Canonical Date

Re: Master Calendar from start date and Enddate

rish_sai — Tue, 02 Aug 2016 20:49:00 GMT

this is a new Project were I have two date ,one is start Date and another End Date which were in same table ,

so I want to create Master calendar for this table and I more thing in frontend I will create list box for year , month, Day , Quarter.

Re: Master Calendar from start date and Enddate

rish_sai — Tue, 02 Aug 2016 20:54:47 GMT

I already tried this script .. but issue is

MasterCalendar:

Load

TempDate AS OrderDate,here I should to (StartDate or EndDate) dn't,if tie to start date I will miss end End date information ...

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

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;