topic Re: Issues using the "Avg" function in QlikView

Issues using the "Avg" function

Thu, 18 Jul 2013 13:55:23 GMT

Hi All,

Has anybody experienced any issues using the avg function within Qlikview?

I have created a straight table containing 14 lines of data (by date) for a number of fields 'units' 'visits' & 'conversions' (units/visits)

I have some text boxes that will display summarised values, the text boxes containing sum("fieldname") are fine but those with avg("fieldname") are not producing the correct values.

Sure I am missing something straightforward or simple somewhere but I cannot seem to fathom it out - can anybody help??

Thanks

(Unfortunately due to business constraints I cannot post the app - but a screenshot demonstrating the prob is below)

Re: Issues using the "Avg" function

Gysbert_Wassenaar — Thu, 18 Jul 2013 13:58:03 GMT

Null values are disregarded by the avg function. So if a record doesn't have a value that record will not be included to calculate the average. That is correct imo, but it may not be what you want. In that case you can divide the sum by the count, i.e. sum(units)/count(units). Or you can try to replace the nulls in the script with real values.

Re: Issues using the "Avg" function

Thu, 18 Jul 2013 14:12:22 GMT

Thanks Gysbert thats useful to know

However not sure that is the problem in this case: if you look at the screenshot (now attached) you should be able to see that for the current selection there is no null values! That's what is baffling me

Re: Issues using the "Avg" function

Gysbert_Wassenaar — Thu, 18 Jul 2013 14:36:10 GMT

Yes, and? What's the result you're expecting? Do you want an average of the sums instead of an average of the actual values themselves? In that case try avg(aggr(sum(units),date))