Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
klaus_feldam
Contributor II

AVG calculation confusion - Different result behaviors

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
avg50.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?

1 Solution

Accepted Solutions
evan_kurowski
Valued Contributor

Re: AVG calculation confusion - Different result behaviors

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.

View solution in original post

4 Replies
Not applicable

Re: AVG calculation confusion - Different result behaviors

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
Valued Contributor

Re: AVG calculation confusion - Different result behaviors

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.

View solution in original post

jsaradhi
Valued Contributor

Re: AVG calculation confusion - Different result behaviors

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

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

Not applicable

Re: AVG calculation confusion - Different result behaviors

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