Set Analysis max value of a range

marcadrebing — Wed, 30 Jun 2021 09:11:03 GMT

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.

Re: Set Analysis max value of a range

tresB — Wed, 30 Jun 2021 09:26:41 GMT

Try like:

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

marcadrebing — Wed, 30 Jun 2021 09:40:53 GMT

You are a rock star!

Works like a charm.

Thanks a lot!