Expression for sum of averages

[#1]

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

[#2]

See file attached.

[#3]

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

[#4]

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

[#5]

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