Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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())
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())
FirstSortedValue() seems to work for me, thanks a lot!