topic Re: Sum averages for one dimension in QlikView

Sum averages for one dimension

Anonymous — Sun, 19 Feb 2017 20:13:37 GMT

Hi all,

I am looking for the correct formula to create a sum of averages for a certain dimension in my datasheet.

My datasheet is organized like this:

Country	Channel	Audience (individuals)
Albania	Channel A	1000
Albania	Channel B	2000
Brazil	Channel C	3000
Brazil	Channel D	4000

I now need to create an average audience per country which needs to be summed up afterwards --> For this test case the result should say 5000.

So far I have tried the following options, but none of them works:

=sum(aggr(avg([Audience (Individuals)]), country))

=sum(aggr(sum([Audience (Individuals)])/count([Audience (Individuals)]) , Country))

In a second step it would be perfect if the formula could leave out those lines of the calculation, that do not have an entry for "Audience (individuals)".

Any suggestions?

Thanks a lot

Re: Sum averages for one dimension

ahaahaaha — Mon, 20 Feb 2017 09:27:56 GMT

Hi Benedikt,

=Sum(Aggr(Avg ( Audience), Country))

Result

Regards,

Andrey

Re: Sum averages for one dimension

Anonymous — Mon, 20 Feb 2017 19:47:39 GMT

Thanks Andrey for that input.

In fact that means I already had the correct formula, however because I worked with an additional dimension (which I did not include here as I thought it was not relevant) the result did not look correct to me. Thanks to your confirmation I looked at it once again and figured out how I had to include the second dimension:

=Sum(Aggr(Avg ( Audience), Country, Year))

Cheers,

Benedikt