Return Dimension based on Lowest Value

[simoncarts]

Good morning All,

I'm trying to create the equivalent of a VLOOKUP / INDEX & MATCH in Qlik where the [Manufacturer] & [Vehicle Description] for the Lowest [Rate] is returned

When I hard-code the lowest [Rate] value into the Expression, the app returns the desired result(s) with no errors (expression below) 
Concat(DISTINCT if([Rate]=(137.56), [Manufacturer] & ' ' & [Vehicle Description]),Chr(10))

When I only replace the hard-coded rate with Min([Rate]) (expression below)
Concat(DISTINCT if([Rate]=(Min([Rate])), [Manufacturer] & ' ' & [Vehicle Description]),Chr(10))

I receive an Error stating 'Error in expression: Nested aggregation not allowed'

I've tried placing Aggr in different parts of the expression which removes the error but then returns no results.

Any ideas?

[Or · Accepted solution]

Perhaps try using FirstSortedValue() instead?

You might also be able to get a result using an internal aggr based on the two dimensions in question, assuming each combination is limited to having a single rate. It'd look something like:

Concat(DISTINCT if([Rate]=(Min(aggr(Only([Rate]),Manufacturer,[Vehicle Description])), [Manufacturer] & ' ' & [Vehicle Description]),Chr(10))

[simoncarts]

Thanks for the quick response!



FirstSortedValue does work and I'm going to use this as my solution. How does it behave when you have 2 Rates being equally the lowest? Ideally I'd like the Manufacturer & Vehicle Descriptions listed if there is more than one 🙂



The Expression you've written out still returns the Nested Aggregation error

[Or]

Right, the outer min() will still cause the nested aggregation issue. Didn't notice that since I didn't catch the outer concat(). You could try using set analysis instead of the nested if(), something along the lines of:

concat({< Rate = {"$(=Max(Rate))"} >} [Manufacturer] & ' ' & [Vehicle Description]),Chr(10))