Count based on date range over time

[colliers_sn]

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.

[sunny_talwar]

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

IntervalMatch

Creating A Master Calendar

[sunny_talwar]

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

[colliers_sn]

Greatly appreciate it Sunny!!