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
