Master Calendar from start date and Enddate

[rish_sai]

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

[Anonymous]

Hi

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

[Anonymous]

Hi

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;

[Anonymous]

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

[rish_sai]

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.

[rish_sai]

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;