topic Re: Count based on date range over time in QlikView

Count based on date range over time

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

Hi All,

I'm try to building a bar chart that represent all the months of the year and does a count of active project based on the start and end date of the project. Example:

Project 1 has a start date of 1/1/2016 and end date of 2/15/2016.

Project 2 has a start date of 1/15/2016 and end date of 3/1/20106.

Project 3 has a start date of 2/28/2016 and end date of 4/2/2016.

Chart would yield this result

Any input for a solution would be greatly appreciated.

Re: Count based on date range over time

sunny_talwar — Wed, 13 Apr 2016 20:50:25 GMT

You would need to create a master calendar and use Interval Match to get this working.

IntervalMatch

Creating A Master Calendar

Re: Count based on date range over time

sunny_talwar — Wed, 13 Apr 2016 20:55:45 GMT

Sample:

Table:

LOAD * Inline [

Project, StartDate, EndDate

Project 1, 01/01/2016, 02/15/2016

Project 2, 01/15/2016, 03/01/2016

Project 3, 02/28/2016, 04/02/2016

];

MinMax:

LOAD Min(StartDate) as MinDate,

Max(EndDate) as MaxDate

Resident Table;

LET varMinDate = Peek('MinDate');

LET varMaxDate = Peek('MaxDate');

DROP Table MinMax;

TempCalendar:

LOAD Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load TempDate as OrderDate,

MonthName(TempDate) as MonthYear

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

IntervalMatch:

IntervalMatch (OrderDate)

LOAD StartDate,

EndDate

Resident Table;

DataModel View

Re: Count based on date range over time

colliers_sn — Wed, 13 Apr 2016 21:04:58 GMT

Greatly appreciate it Sunny!!