I would consider breaking down the EstimatedHours to the smallest granularity needed using a WHILE loop, as demonstrated here:
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.
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?