Forum Discussion

IvanWeiss's avatar
Qrew Captain
2 years ago

Estimated Hours Forecast by Week - Maybe Formula Queries?

Hello everyone, thanks in advance for the help!  I am trying to build a bar graph that does the following function as part of our system for better resource planning for our designers time.

X Axis:  Week #'s for the next 8 weeks
Y Axis:  Total Estimated Hours for that week​
Series:  The designer name

Goal Line of 30 hours a week.  

The intent is so that we can see workload over the next 8 weeks to determine if we are over or under allocated our resources to projects.  If you have a better idea, I am all ears but this is what we came up with!

For the X axis.  I do not have a field to give me this.  I have a Start Date and a Due Date for each task.  My thought was that what I need to do is get to my next week (so Sunday or Monday I guess) and somehow that becomes Week 1 through that Friday Date (or Saturday).  Increment by 1 to get to the next week.  Or if at all possible we could have a date range print like 1/2-1/6 and the next bar be 1/9-1/13

For the Y axis.  Within each of those week windows (1-8) or the date ranges above I would take the estimated hours per day field I already created and multiply that by the number of days in that week the task occurs.  So if a task starts on a Wednesday of Week 1 and ends on a Tuesday of week 2 it would get 3 days in week 1 and 2 days in week 2.  Each one respectively multiplied by the number of assigned hours by day (which I already have as a field)

For the series, I already have this so that is easy.

For the goal, that is easy too.

I am kind of at a loss of how to start here beyond getting the estimated hours per day which I already did the calculation on.  That is the total # Of hours / WeekdaySub([Due Date],[Start Date] and that calculation works well.

Appreciate the help in advance!​

Ivan Weiss
No RepliesBe the first to reply