Skip to main content
Announcements
Introducing a new Enhanced File Management feature in Qlik Cloud! GET THE DETAILS!
cancel
Showing results for 
Search instead for 
Did you mean: 
anthonyeeles
Contributor II
Contributor II

FirstSortedValue with Set Analysis

Hi everyone,

I am struggling to get a "lowest value" type solution. I have a table which compares the price and distance of pairs of locations in my database, like this:

LocationID ComparedLocationID ComparedLocationName ComparedLocationPrice ComparedDistance
12355 12272 Location 844 26 0.18912645163311
12355 12386 Location 752 30 0.27766228681346
12355 12762 Location 345 40 0.31924088539052
12355 12518 Location 541 36 0.34147527922903
12355 12545 Location 586 36 0.34147527922903
12355 12476 Location 84 38 0.41288297293363
12355 12353 Location 880 48.5 0.53549753898979
12355 12335 Location 220 48.5 0.57002562592256
12355 12758 Location 780 26 0.66663773606327
12355 12080 Location 26 30 0.70992792036625
12355 12222 Location 43 35 0.71534669915399
12355 12701 Location 574 40 0.7449042128228
12355 12703 Location 808 40 0.79096353125306
12355 10123 Location 676 38 0.81746758231713
12355 12618 Location 351 27 0.90440058147854
12355 12728 Location 532 40 0.92123159170605
12355 12617 Location 68 40 1.0075602368222
12355 12354 Location 816 48.5 1.2126078777738
12355 12795 Location 802 26 1.3312047153253
12355 12590 Location 868 38 1.3314522539974
12355 10114 Location 719 38 1.3794920387649
12355 12702 Location 293 40 1.4490457603866
12355 10752 Location 72 26 1.6013994998343
12355 12879 Location 540 30 1.627419095761
12355 12580 Location 558 25 1.6767955213875

 

The dimension in my chart is LocationID. I am then trying to get the ComparedLocationName of the Location with the lowest ComparedLocationPrice as long as the ComparedDistance is within, say, 5 miles.

I had assumed I could use FirstSortedValue with some set analysis:

=FirstSortedValue({<ComparedDistance={'<=5'}>} ComparedLocationName, ComparedLocationPrice)

The expression evaluates OK, but returns empty cells.

Googling seems to suggest I can't use Set Analysis in FirstSortedValue, but I can't seem to find an alternative. Other examples use an AGGR but I'm not clear how to do that in this context. 

All suggestions welcome!

Thanks!

 

Labels (1)
1 Reply
SerhanKaraer
Creator III
Creator III

Hello anthon,

Try this expression:

Concat({<ComparedLocationPrice={"$(=min({<ComparedDistance={"<=5"}>} TOTAL ComparedLocationPrice))"}>} ComparedLocationName, ';')

If there happens to be multiple comparedlocationnames, you can show all them.