Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Date | Person | Description | Time | ||
---|---|---|---|---|---|
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.
Date | Person | Total Time | ||
---|---|---|---|---|
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.
Thanks
JP
See file attached.
Hi Lakhina
Thanks for the response. Unfortuantely I have the PE of qlikview at this location so can't open your file. Any chance you can post the script / expression that you used.
Thank
JP
Hi Jp,
Try the following as your expression for FTE, this will work irrespective of selection in Discription :
sum({<Discription=>}Time)/sum(TOTAL<Date,Person>{<Discription=>}Time)
Regards
Rahul
Hi Rahul
It wasn't exactly what I was after, but it did start me down a number of paths I didn't know before and I think I have found a wokable solution. Part of the challenge was needing to ignore certain selections, which your suggestion definitely helped me solve.
Thanks
JP