Set Analysis max value of a range

[marcadrebing]

Hi everybody, 

does anybody have an idea how display the last value in a data set with missing data points?

Here an example

Dim	-10	-9	-8	-4	-3	-2	-1	0
Value	200	180	170	120	110	100	80	50

Using a variable vDim =-6, I want to display the last value before Dim = -6, in this example (-8/170).

Since the variable is supposed to be flexible in the frontend, I don't want to fill the gaps in the data model to keep the database small. 

That's why I tried to solve this with set analysis. My first idea was something like 

Dim={"$(=Max(<=$(=vDim))"}

So, in words: Select all values smaller than -6 and then display the maximum value of that subset (here -8/170)

Does anyone have an idea, how to do that?

Thanks, 

Marc 

I tried for some hours to find a solution in the forum, but I couldn't find anything. 

[tresesco · Accepted solution]

Try like:

=FirstSortedValue( {<Dim={"<$(=$(vDim))"}>} Value, -Dim)

[marcadrebing]

You are a rock star!

Works like a charm.

Thanks a lot!