4 Replies Latest reply: Aug 20, 2012 11:21 AM by Jean-Paul Camelbeek RSS

    Expression for sum of averages

      Hi All

       

      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:

       

       

      DatePersonDescriptionTime
      10 March 2012JPTransaction4 hours
      10 March 2012JPAdvice3 Hours
      10 March 2012JPAdmin2 Hours
      11 March 2012JPTransaction8 Hours
      12 March 2012JPAdvice4 Hours
      12 March 2012JPAdmin4 Hours
      10 March 2012GregTransaction6 Hours
      10 March 2012GregAdvice2 Hours
      11 March 2012GregAdmin3 Hours
      11 March 2012GregAdvice6 hours

       

      I also have a field which I calculate during loading which is the total time logged for the day.

       

      DatePersonTotal Time
      10 March 2012JP9
      11 march 2012JP8
      12 March 2012JP8
      10 March 2012Greg8
      11 March 2012Greg9

       

      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.

       

      Thanks

      JP