topic How to create calender with Start date and end date in QlikView

How to create calender with Start date and end date

tulsidaskhan — Tue, 31 May 2016 10:55:09 GMT

Hi,

I have two columns as Start date and End date in my data. I want to connect my data with calendar. how to join calendar with this data so as to connect calendar with both start and End date.

Thanks,

Piyush Jain

Re: How to create calender with Start date and end date

Peter_Cammaert — Tue, 31 May 2016 12:10:28 GMT

Have a look at Canonical dates:

Qlik Design Blog : Canonical Date | Qlik Community

Linking to two or more dates | Qlik Community

Peter

Re: How to create calender with Start date and end date

beck_bakytbek — Tue, 31 May 2016 12:17:32 GMT

hi jain. this is very simple example that helps you to understand this issue (with only working days)

try this code:

let vStartDay = date('01/01/2010');
let vEndDate = date('31/12/2016');

for x = vStartDay to vEndDate

tempCalendar:
load $(x) as Date AutoGenerate(1);

next x

NoConcatenate

Working_Calendar:
Load Date, weekday(Date) as WeekDay, week(Date) as Week, Month(Date) as Month, Year(Date) as Year, floor(month(Date)/3)+1 as Qtr

resident tempCalendar

where WeekDay(Date) <> 'Sat' and WeekDay(Date) <> 'Sun';

drop table tempCalendar;

if you Need more examples to this issue. so let me know

beck

Re: How to create calender with Start date and end date

sunny_talwar — Tue, 31 May 2016 14:14:48 GMT

You can use intervalMatch:

IntervalMatch‌

or use While loop to expand start to end dates into a date field:

Creating Reference Dates for Intervals