topic Re: Avg function in QlikView

Avg function

anthony_kinsell — Wed, 07 May 2014 10:36:18 GMT

Hi,

Just wondering can anyone clarify how this function actually works - to me it seems to calculate correctly sometimes & other times not - see table below:

UnitDesc	YearMonth	Jan-2013	Feb-2013	Mar-2013	Apr-2013
Apartment 1		1,300	1,300	1,300	1,300
Apartment 12		1,300	1,300	1,300	1,300
Apartment 23		1,300	450	1,350	1,350
Apartment 34		1,300	1,300	1,300	641
Apartment 45		1,300	1,300	1,300	1,300
Total		1,300	936	1,310	1,089

Jan & Mar are correct but Feb & Apr aren't - expression is a simple Avg(Amount)

Re: Avg function

kaushiknsolanki — Wed, 07 May 2014 10:40:00 GMT

Hi,

The difference may be because you might have some records for the month of Feb and Apr where Amount field has null values, so the avg will take the avg of all the records, and may be thus total is not correct.

Try this function.

sum(aggr(avg(Amount),UnitDesc,YearMonth)

Regards,

Kaushik Solanki

Re: Avg function

anthony_kinsell — Wed, 07 May 2014 10:49:52 GMT

Hi,

I had just realised that. I did a less sophisticated formula - but probable has the same result:

Sum(Amount) / Count(DISTINCT(UnitDesc))

Cheers

Re: Avg function

kaushiknsolanki — Wed, 07 May 2014 10:53:14 GMT

Hi,

then you can try this.

Sum(Aggr((Sum(Amount)/Count(Distinct UnitDesc)),UnitDesc,YearMonth)

Regards,

Kaushik Solanki

Re: Avg function

Wed, 07 May 2014 11:00:44 GMT

Hi,

Another possible way, make average without nulls with set analysis: Avg({<Amount={'*'}>} Amount)

Re: Avg function

anthony_kinsell — Wed, 07 May 2014 11:05:38 GMT

Thanks Guys,

Think my simple formula works best in this case as the problem isn't null values it's numerous values in a given month for a given apartment - for example if an apartment has 4 values in 1 month I want the total of these but divided by 1 not 4. Also if an apartment has no value it should still reduce the overall average.

Regards

Anthony