I am struggling with displaying the total workload for my staff at a given time.
I want to display in a linechart "the total workload for the beginning of each month: 2013-01, 2013-02, 2013-03 etc."
The following is the data that I am working with:
PersonID
ProjectID
Project
StartDate
EndDate
PercentageWorkLoad
ADAD9999
1
Windows
2011-01-04
2013-01-22
50
ADAD9999
2
Doors
2012-12-14
2013-04-22
50
JOAL1158
0
NotWorking
2012-08-23
2012-08-31
0
JOAL1158
3
Roof
2012-09-01
2012-10-30
100
DADA8822
0
NotWorking
2012-01-22
2012-12-31
0
NINI1234
4
Floor
2012-09-25
2013-05-01
50
NINI1234
1
Windows
2012-09-25
2013-05-01
50
NINI1234
0
NotWorking
2012-05-02
2013-06-21
0
OLOL1122
2
Doors
2012-09-25
2013-05-01
50
OLOL1122
5
Ceiling
2012-09-25
2013-06-01
50
OLOL1122
6
Tapestry
2011-01-01
2011-10-12
75
SVSV1212
4
Floor
2012-01-01
2013-01-01
50
SVSV1212
0
NotWorking
2013-01-02
2013-06-21
0
SVSV1212
0
NotWorking
2012-01-01
2013-01-01
0
I have created a table with all the months ranging from the first StartDate to the last EndDate:
MonthStart([StartDate] + IterNo()-1) as MonthStart,
Resident Employee while [StartDate] + IterNo()-1 <= [EndDate];
But now I am stuck in visualizing this in a line chart. I need to figure out how to get:
The sum of the percentage where the StartDate is less than the MonthStart and the EndDate is more than the MonthStart and dividie this with the distinct number of Employees.
This should then be expressed in my chart so that I can see that 2012-01 the total workload was X%, 2012-02 the total workload was Y% etc.
what is missing from the example is that I need the total percentage for all employees at a given time. Therefore I need to divide the workload percentage with the number of employees that were active at that point.
So if I take 2012-10 as an example the sum of percentage is 150, but the average is 75 since two employees was within this range.
If I remove the PersonID from the chart, the sum of percentage will exceed 100%.
