This content has been marked as final. Show 4 replies
I need some help with the summing averages which I use in an expression for a chart.
I have people logging time against tasks as in the table below:
|10 March 2012||JP||Transaction||4 hours|
|10 March 2012||JP||Advice||3 Hours|
|10 March 2012||JP||Admin||2 Hours|
|11 March 2012||JP||Transaction||8 Hours|
|12 March 2012||JP||Advice||4 Hours|
|12 March 2012||JP||Admin||4 Hours|
|10 March 2012||Greg||Transaction||6 Hours|
|10 March 2012||Greg||Advice||2 Hours|
|11 March 2012||Greg||Admin||3 Hours|
|11 March 2012||Greg||Advice||6 hours|
I also have a field which I calculate during loading which is the total time logged for the day.
|10 March 2012||JP||9|
|11 march 2012||JP||8|
|12 March 2012||JP||8|
|10 March 2012||Greg||8|
|11 March 2012||Greg||9|
I now want to calculate the equivalent FTE being spent on a task.
So if someone clicks Advice and 10 March as a filter then you get:
JP - Advice = 3 / Total Time Logged for 10 March 2012 = 9 = 0.33
Greg - Advice = 2 / total time logged for 10 March 2012 = 8 = 0.25
Therefore FTE spent on Advice = 0.58
I have not been able to figure out an elegant expression for this that works when there is a filter set and when there is no filters set.
I hav tried:
(sum(timeontask) / sum(totaltime)) * number of people - this give a distored picture
sum(timeontask / totaltime) - this seemed the most logical to me but was clearly not correct.
Any suggestins much appreciated.