topic Re: How to create calender with start-date and end-date with in same table in QlikView

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

shabarish0587 — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

Brett_Bleess — Thu, 02 Jan 2020 16:31:30 GMT

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

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

Frank_Hartmann — Thu, 02 Jan 2020 16:45:10 GMT

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;

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

Kushal_Chawda — Thu, 02 Jan 2020 18:43:13 GMT

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