Return ID at Min/Max Location

[#1]

Hi all,

I'd like to create a (straight table) expression that that returns an ID (or some specified field) given the location of a min/max value. For instance, I'm using the Min() function to to find the minimum value in a range on prices, and now I'd like to move (horizontally) within the same row on the data sheet to return a different ID (in my case, a date) at that location.

In simpler terms, if I find that the minimum purchase price of product XYZ was $1, I want to know what the date on which that product was purchased (it might have been purchased at a higher price on a different date).

I cannot simply ad the date field as an expression/dimension because it will return multiple values

ProductID	MinVal	Date@Min
1	$1	?
2	$4
3	$5
4	$2
5	$3

[rustyfishbones · Accepted solution]

Did you try this

=DATE(FIRSTSORTEDVALUE(Datefield,-MinVal))

here is how it looks on my App, I have different fields of Course!!

[#3]

That works perfectly!... in most cases. What if the min/max value appears in the data several times on several different dates (the product ID was purchased at the same min value on 3 different dates, for instance). In that case, I'm returning a '-' because there are multiple selections from which to choose. I don't care too much which date it chooses, as long as I have one for reference. Any ideas?

Thanks,

Paul

[rwunderlich]

Add the DISTINCT keyword in the FirstSortedValue() function

FirstSortedValue(DISTINCT ...

-Rob

[rustyfishbones]

See Rob's answer below, can you give out the points please!

[#6]

Hey Rob and Alan,

You guys are geniuses! Thanks for the help - worked like a charm.

On a side note, any idea why it underlines the expression in red when you ad the DISTINCT keyword, even thought it is an acceptable expression ("expression ok" in the expression box)?

-Paul

[rwunderlich]

I think it's just a case of the syntax checker being wrong. There are a few examples of this. Like the "WHEN" prefix which always flags as red:

when A=1 load * from myfile.csv;

-Rob

[#8]

Hey guys,

On a similar note, what if I want to use FirstSortedValue to find the location one larger than the minimum (minID+1), or one smaller than the maximum (maxID-1)?

I assume it would be something similar to the above suggestion, but adding a "+1" or "-1" somewhere, but I can't get it to work

=DATE(FIRSTSORTEDVALUE(Datefield,-MinVal+1))