topic Set Analysis problem in App Development

Set Analysis problem

schmidtj — Tue, 27 Apr 2021 07:13:34 GMT

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?

Re: Set Analysis problem

GaryGiles — Tue, 27 Apr 2021 09:57:49 GMT

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())

Re: Set Analysis problem

schmidtj — Tue, 27 Apr 2021 10:56:17 GMT

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