Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this: avg(aggr(if(rank(max(Score))<4,max(Score)),Country,HQ))
Try this: avg(aggr(if(rank(max(Score))<4,max(Score)),Country,HQ))
I would use
=avg(aggr(if(rank(max(Score))<4,max(Score)),HQ, Country))
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))