Avg function

[anthony_kinsell]

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)

[kaushiknsolanki]

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

[anthony_kinsell]

Hi,

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

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

Cheers

[kaushiknsolanki]

Hi,

then you can try this.

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

Regards,

Kaushik Solanki

[#5]

Hi,

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

[anthony_kinsell]

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