topic Re: Creating average by unique countries in QlikView

Creating average by unique countries

gabormajdan — Thu, 29 Oct 2015 13:17:37 GMT

Hi All,

I want to create the average score of an HQ, based on its top 3 highest scoring subsidiaries. There is one condition: the top 3 has to be composed of unique countries, i.e. the average cannot contain more than 1 subsidiary based in the same country. If a such a case occurs, we will take the highest score into account. See the example below.

Here is the source data:

HQ	Subsidiary	Country	Score
HQ_1	Company_A	FRA	6
HQ_1	Company_B	ITA	5
HQ_1	Company_C	FRA	4
HQ_1	Company_D	GER	2
HQ_2	Company_E	ESP	7
HQ_2	Company_F	ITA	4
HQ_2	Company_G	GER	3
HQ_2	Company_H	ESP	2

The resulting table would look like this:

Rank	HQ	Average Score
1.	HQ_2	4.67
2.	HQ_1	4.33

Notice that for HQ_1 we had to take the average of companies A, B and D, because A and C are both from France and A has the highest score. Therefore C was omitted. H was omitted because it is not in the top 3. (note: we don't have to deal with the dilemma of an HQ having subsidiaries from only 1 country)

I have the following piece of code, that does the job of picking the top 3 of any HQ, but I cannot get it to exclude a duplicate country.

avg(

if(

aggr(num(rank(sum(Score),1)), HQ, Subsidiary) < 4,

sum(Score)

)

Thanks in advance for your help!

Gabor

Re: Creating average by unique countries

Gysbert_Wassenaar — Mon, 02 Nov 2015 16:31:20 GMT

Try this: avg(aggr(if(rank(max(Score))<4,max(Score)),Country,HQ))

Re: Creating average by unique countries

swuehl — Mon, 02 Nov 2015 16:53:49 GMT

I would use

=avg(aggr(if(rank(max(Score))<4,max(Score)),HQ, Country))

Re: Creating average by unique countries

gabormajdan — Wed, 04 Nov 2015 09:36:22 GMT

Thanks! I had to tweak it a bit myself, because I gave a simplified version of the problem, but overall this was the right solution (in reality the Score is a lengthy calculation so I had to embed another aggr in the max, over Country, HQ and Company fields).

Something like this:

avg(aggr(if(rank(max(aggr(Score,Country,HQ,Company)),<4,max(aggr(Score,Country,HQ,Company))),Country,HQ))