Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
chrismtb
Creator
Creator

using date generator over multiple years

Hi, I have some code (below) which is causing me a headache.

I have a Gantt Chart that I am pulling together. Each of my projects has a start date (GanttStart) and an end date (GanttEnd). I have written a code loop to add records in the gaps between the start and end date. These are in monthly increments with each project starting on the 1st of the month.

e.g. a project starts on 01/01/21 and ends 01/12/21 therefore the code will add a data point for 01/02/21, 01/03/21 etc.

 

This works great - as long as the start and end date are both this year. My issue is that i have some projects starting in January 2021 and ending in November 2022.

Can someone suggest how i can approach the problem?

 

an extract of my current code:

 

GANTT_CELLS:
Load
GanttId,
MonthCounter,
GanttStart,
GanttEnd,
Status,
;
Load
Distinct GanttId,
MakeDate(2021,((Month(GanttStart)-1)+IterNo()),01) as MonthCounter,
GanttStart,
GanttEnd,
1 as Status
Resident GANTT_Data
While
IterNo() <= Month(GanttEnd) - Month(GanttStart) + 1;

Labels (1)
1 Solution

Accepted Solutions
marcus_sommer

You need to consider the years, too. Maybe with something like this:

...
Monthstart(GanttStart, IterNo()-1) as MonthCounter,
...
While
IterNo() <= (year(GanttEnd) * 12 + Month(GanttEnd)) - (year(GanttStart) * 12 + Month(GanttStart) + 1);

- Marcus

View solution in original post

2 Replies
marcus_sommer

You need to consider the years, too. Maybe with something like this:

...
Monthstart(GanttStart, IterNo()-1) as MonthCounter,
...
While
IterNo() <= (year(GanttEnd) * 12 + Month(GanttEnd)) - (year(GanttStart) * 12 + Month(GanttStart) + 1);

- Marcus

chrismtb
Creator
Creator
Author

Thanks Marcus - works like a charm!