How to create calender with start-date and end-date with in same table

[shabarish0587]

Hi Friends,

                I am facing one facing while creating master calendar with start date and end date. can any one help me on this. i am sharing sample excel file.

 in sample file i have columns like stream, Iteration, start-date and end-date.

by using common field Iteration i created like this

Table1:

Load Stream,Team Name, Iteration, Start-date, end-date from excel

Link_Table:

Load Itearation

Start_date as Date

Resident Table1

concatenate

Load Itearation

end_date as Date

Resident Table1

after creating this, i created a master calendar. but here i am getting issue when i am selecting date column from master calendar . it is not selecting any data related to Iteration. it is selecting year,month which is related to master calendar data. but when i am checking at tableviewer both tables has linked, but  fuctionality is not working. can any one help on this. Thanks in advance.

[Brett_Bleess]

Have a look at the following Design Blog post on creating a Master Calendar, hopefully that will get you on the right track with things.  I am going to provide another Calendar related post too just in case and lastly the base Design Blog URL in case you want to search there yourself:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

[Frank_Hartmann · Accepted solution]

see attached qvw.

1:
LOAD Stream, 
     [Team Name], 
     Iteration, 
     Start_Date, 
     End_Date,
     MonthName(Start_Date) as MonthYear
FROM
[C:\Users\admin\Downloads\Iteration.xlsx]
(ooxml, embedded labels, table is Sheet1);

Link_Table:
LOAD min(Start_Date) as minDate,
     max(End_Date) as maxDate
Resident 1; 

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));
LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Link_Table; 

TempCalendar:
LOAD
  $(vMinDate) + IterNo() - 1 as Num,
  Date($(vMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate); 

MasterCalendar:
LOAD
  TempDate as Date,
  Week(TempDate) as Week,
  Year(TempDate) as Year,
  Month(TempDate) as Month,
  Day(TempDate) as Day,
  Weekday(TempDate) as WeekDay,
  'Q' & ceil(month(TempDate) / 3) as Quarter,
  'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as QuarterYear,
  MonthName(TempDate) as MonthYear,
  Week(TempDate)&'-'&Year(TempDate) as WeekYear
Resident TempCalendar
Order By TempDate ASC;
DROP Table TempCalendar;

[Kushal_Chawda]

You can generate all Dates between Start date & End date using below method

T1:
LOAD
    Stream,
    "Team Name",
    Iteration,
    date(Start_Date+IterNo()-1) as Date,
    End_Date
FROM [lib://Download/Iteration.xlsx]
(ooxml, embedded labels, table is Sheet1)
while Start_Date+IterNo()-1<=End_Date;

Now using this Date field, you can generate the master calendar. It should work fine