topic Average in QlikView

Average

Mon, 26 Jan 2015 10:55:04 GMT

Hi,

I have the following table on qlikview. I am only concerned with the 'Total' row. Total # Apps is correct. However under Total row, Avg Decision Time Hrs (3.80) and Apps Decisioned per Day (3.09) are not correct. Currently Total Average for the above two columns is simple. It's taking average of all rows. But the reality is that each staff has different no of applications Therefore the average 3.80 & 3.09 are wrong. Any way to find correct Total Average?

Staff Name	# Apps	Avg Decision Time (Hrs)	Apps Decisioned per Day
A	407	2.45	5.99
B	422	3.18	5.94
A	376	3.16	5.30
B	301	3.88	4.85
A	271	2.89	4.30
B	261	4.24	4.08
A	182	4.37	3.87
B	195	4.43	3.82
A	217	4.17	3.50
B	189	2.45	3.15
A	198	6.33	2.96
B	207	6.23	2.80
A	164	4.33	2.52
B	51	1.71	2.43
A	41	0.50	1.71
B	1	0.52	1.00
A	1	4.12	1.00
B	1	2.20	1.00
A	8	6.27	0.80
B	20	8.56	0.80
Total	3,513	3.80	3.09

Re: Average

Gysbert_Wassenaar — Mon, 26 Jan 2015 11:03:21 GMT

You want the average of the averages? Try avg(aggr( ...avg_decision_time_expression_here... , [Staff Name]))

Re: Average

agomes1971 — Mon, 26 Jan 2015 11:05:41 GMT

Hi,

be more explicit by the way you want the calculation done...

Regards

André Gomes

Re: Average

Mon, 26 Jan 2015 11:07:06 GMT

Yes I want to take average of averages. however currently they way its doing now is not correct

Re: Average

Mon, 26 Jan 2015 11:09:13 GMT

Hi Andre,

I want to get average of all averages by taking each average based on their applications. Currently it just sums up all averges and divide by numner of staffs which is wrong.

Re: Average

Mon, 26 Jan 2015 11:12:02 GMT

Hi Gsybert,

Individual average for each staff is correct however the issue is only under Total. How can I write the above expression to only get under Total average??

Re: Average

Mon, 26 Jan 2015 13:04:41 GMT

Hi Shoaib,

try

If( Dimensionality()=0,

avg(aggr( [avg_decision_time_expression], [Staff Name]))

,[avg_decision_time_expression]

)

and replace with your avg expression.

If that doesn't work maybe post up an example app

hope that helps

Joe

Re: Average

kji — Mon, 26 Jan 2015 13:25:25 GMT

You should be using "Expression Total" (Total Mode on the Expressions tab), but it sounds like you currently have it set to "Average of Rows".

Re: Average

Mon, 26 Jan 2015 14:09:31 GMT

Hi Joe,

I am using "Average of Rows" under Total Mode. But it's not right way. How can I put your expression because there is no option to write expression under Total Mode

Re: Average

MarcoWedel — Mon, 26 Jan 2015 14:41:32 GMT

Please post your expected result and the way you calculated it.

Re: Average

Mon, 26 Jan 2015 15:13:48 GMT

Marco,

Sorry I posted wrong table. suppose I have the following table. The way its calculating now is not correct because each staff average is bassed on different no of apps. Only 2 applications took 50 hrs but 100 took 2 hrs. So these 2 apps skewed the total average to 26 which is wrong.

Staff Name	# Apps	Avg Decision
		Time (Hrs)

A	2	50
B	100	2
	102	26.0

Re: Average

kuba_michalik — Mon, 26 Jan 2015 15:44:59 GMT

So the total value for average should be total decision time divided by total number of apps? Which is the same as calculating the average of averages, weighted by number of apps?

Because if so, then you should just switch the total mode for this expression to "Expression Total", as suggested by Johan, instead of calculating an average of rows. Does that not work?

Re: Average

Mon, 26 Jan 2015 16:24:02 GMT

So you want to know the average response rate among the team.

So if person a took 3 hours and person b took 2 hours, you want to see 2.5 hours as your average regardless of how many applications each person made?

Not saying I have the answer, just trying to be helpful in clarifying your requirements.

Re: Average

Mon, 26 Jan 2015 16:47:20 GMT

I want to consider total apps per person as well.

Re: Average

Mon, 26 Jan 2015 16:49:12 GMT

Why don't you knock some data up with the correct totals in excel or something, would help being able to see what you are calling the 'correct' average etc

Re: Average

Mon, 26 Jan 2015 16:56:04 GMT

His problem is better described here http://community.qlik.com/thread/64633.

Re: Average

Mon, 26 Jan 2015 17:00:12 GMT

data as below. I dont know the correct avg but definitely the current 3.87 & 3.11 are wrong

Staff Name	#Apps	Avg Decision Time (Hrs)	Avg Apps Decisioned per Day
A	423	2.57	5.88
B	433	3.38	5.77
C	412	3.22	5.49
D	315	4.02	4.92
E	292	3.08	4.36
F	284	4.18	4.18
G	213	4.74	3.87
H	182	4.37	3.87
I	238	4.23	3.55
J	213	2.55	3.33
K	211	6.46	2.97
L	217	6.29	2.78
M	170	4.37	2.46
N	51	1.71	2.43
O	46	0.46	1.77
P	1	0.52	1.00
Q	1	4.12	1.00
WE	1	2.20	1.00
ERE	9	6.40	0.82
WER	20	8.56	0.80
	3,732	3.87	3.11

Re: Average

Mon, 26 Jan 2015 17:06:52 GMT

Are tour expressions calculated in Qlikview? If so the expression total radio button on the relevant expression in properties should give you the value that you need.

Re: Average

Mon, 26 Jan 2015 17:15:00 GMT

I tried but did not look correct. will try again