AVG calculation confusion - Different result behaviors

[klaus_feldam]

This should be a quick one for one you Qlik script gurus:

I have a simple challenge with an AVG calculation, where the script turns up different results - depending on which object I am using for the calculation.

Data:

	7
	26.777778
	36.75
	41
	45
	48.333333
	51
	51.25
	55.166667
	64.142857
	64.666667
	111
avg	50.17394183

Simple, right!?

My table formula gives me the expected result IF my Total Mode on the Expressions tab is set to 'Average of Rows'.

Expression total gives me 52.761905, which is wrong.

Also, if I am making the average calculation on this data in a text object, I get also get the wrong figure; 52.761905.

My formula to get the individual rows, and ultimately overall average, is as follows:

avg(if([Condition A] = 'True',[Closed Date]-[Start Date]))

I am simply trying to calculate the average days to close if Condition A is true + get the overall average across all returned records.

Please help me understand how my text object formula (needed for a dashboard) is getting the wrong overall average;

52.761905 instead of 50.17394183. Which change should I make to my formula to get the expected results?

[#2]

its not wrong,

When 'Average of rows is selected' it does sum/no of rows in the straight table(in this case 12)

When Expression total is selected its sum/no of actual rows in the back-end table(could be more).

So depending on which one you want to show please pick one.

[evan_kurowski · Accepted solution]

Yeah, verify your text box denominator size matches the denominator of the script generated avg:

count(if([Condition A] = 'True',[Closed Date] - [Start Date]) 

For the table, the Total Expression of Avg could easily calculate an "Avg of the Avg" and that doesn't have to match the granular level Total (Sum) / Total (Count) calculation.

[Anonymous]

http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/29/averages

here is a link that should help in understanding these different averages

[#5]

Hi,

Yes you have to be careful when you use average function in QlikView. In most common case, you have add manually formula for average.

Maybe when you add additional lines in some bar charts is ok, to add line average function. But when you need from correct results, use formula in the expression which you'll create manually.

Regards,

Venelin