Set Analysis problem

[schmidtj]

Hi,

i am configuring a map chart in Qlik Sense and i have a problem getting the appropriate color code for my map.
Here is some example date to show what my problem is:

Country	ColorCode	Sales
France	C1	100
France	C2	50
Germany	C2	200
France	C8	250
Italy	C3	300
Italy	C1	10

In my color kpi in the chart configuration i need to get the color per country which has the lowest sales.
So for France for example the color should be C2.

I cant get my head around though how to do this in the kpi fomular using Set Analysis.
For other reasons i cant really edit the data script.

Can somebody give me some advice?

[GaryGiles · Accepted solution]

I think you want to use a combination of the aggr() function and the FirstSortValue() function.

=Aggr(FirstSortedValue(Distinct ColorCode,Sales),Country)

This will give you the ColorCode associated with the row containing the lowest Sales for each Country.

Are you converting your ColorCode field to a specific color?  I used the following to test:

=Pick(Match(Aggr(FirstSortedValue(Distinct ColorCode,Sales),Country),'C1','C2','C3'),Red(),Green(),Blue())

[schmidtj]

FirstSortedValue() seems to work for me, thanks a lot!