How to average hours between a start and end date?

[drminaker]

I'm wondering if anyone can help me with creating a chart that will show hours averaged over the duration of a start and end date?

I understand the basics of the master calendar and have created this. I've also been able to calculate the business days/hours between the start date and end date. My problem is that I can't figure out how to average the estimated hours across a span of months and get the months to show up on the chart.

I've been reading though a lot of the community posts and perhaps IntervalMatch is what I need to use, but not sure how to get that to work.

Thanks!

Source data:

StartDate	EndDate	EstimatedHours	ProjectName	Staff
7/1/2015	11/30/2015	500	Project A	Bill
8/1/2015	09/30/2015	300	Project B	Bob

What I'd like my chart to look like:

[marcus_sommer]

You could try something like this:

sum(EstimatedHours) / count(distinct Month)

if you have connected your StartDate and EndDate with the master calendar: IntervalMatch.

- Marcus

[swuehl · Accepted solution]

I would consider breaking down the EstimatedHours to the smallest granularity needed using a WHILE loop, as demonstrated here:

Creating Reference Dates for Intervals

SET DateFormat = 'M/D/YYYY';

INPUT:
LOAD Recno() as ID, * INLINE [
StartDate, EndDate, EstimatedHours, ProjectName, Staff
7/1/2015, 11/30/2015, 500, Project A, Bill
8/1/2015, 09/30/2015, 300, Project B, Bob
];

ID_x_Dates:
Load ID,
    EstimatedHours / (EndDate - StartDate + 1) as DailyEstimatedHour,
    Date(StartDate + IterNo() -1 ) as ReferenceDate
Resident INPUT
While IterNo() <= EndDate - StartDate + 1 ;

Here I used a date as finest granularity, which you can easily aggregate to week / month / quarter using a master calendar connected to ReferenceDate. If you only need a Monthly view, you can also change the code to iterate over Months, not dates.

[drminaker]

Thanks so much for this. I applied it to my real data and it works great! As Marcus mentioned, I was sure the solution had something to do with intervals, but just wasn't sure how it worked.

Another question for you -- if I wanted this to span the minutes/hours only over work days where would be the best place to apply Networkdays function? The loadscript, master calendar, custom dimension?

Thanks!

R.

[drminaker]

Thanks for pointing me to that link. I'm determined to become expert on how all this works!

R.