Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

Tags (1)
4 Replies
Not applicable

Re: Expression for sum of averages

See file attached.

Not applicable

Re: Expression for sum of averages

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

Not applicable

Re: Expression for sum of averages

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

Not applicable

Re: Expression for sum of averages

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