Interval match function in Calender Script

Anonymous — Sun, 04 Mar 2018 19:07:07 GMT

Hi All,

I am creating a gantt chart in a pivot table. It has to look as it looks in excel. A chart will not do.

There are multiple dates: StartDate1, EndDate1, StartDate2, EndDate2, StartDate3, EndDate3, TargetDate

I have manage to put all these dates in the pivot table and it looks fine.

The issue I am facing now is filling in the gaps between the 'StartDate's and 'EndDate's.

The Start and End dates are grey, purple and orange

Brown and yellow are Target dates.

I am trying to implement an interval match function to create a range between the Start and End dates but with no success

Below is the Calender script I am using:

//===================================================

// Subroutine to Generate Calendar.

//===================================================

SUB CalendarFromField(_field, _calendar, _prefix)

[$(_calendar)]:

// Generate Final Calendar

LOAD

[$(_field)]

,year([$(_field)]) as [$(_prefix)Year]

,month([$(_field)]) as [$(_prefix)Month]

,day([$(_field)]) as [$(_prefix)Day]

,weekday([$(_field)]) as [$(_prefix)Weekday]

;

// Generate range of dates between min and max.

LOAD

date(DateMin + IterNo()) as [$(_field)] // Link Field

WHILE DateMin + IterNo() <= DateMax

;

// Find min and max of date field values.

LOAD

min(datefield)-1 as DateMin

,max(datefield) as DateMax

;

// Load date field values.

LOAD

FieldValue('$(_field)', RecNo()) as datefield

AutoGenerate FieldValueCount('$(_field)');

END SUB

After creating individual Date tables using resident load, I call them:

CALL CalendarFromField('Date', 'CommonCalendar', '');

CALL CalendarFromField('StartDate1', 'CalendarSD', 'SD');

CALL CalendarFromField('EndDate1', 'CalendarED', 'ED');

Can somebody help me implement Interval match in the script above. I'd appreciate the help. Thanks

topic Interval match function in Calender Script in QlikView