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
------------------------------
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
------------------------------