Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Did you try this
=DATE(FIRSTSORTEDVALUE(Datefield,-MinVal))
here is how it looks on my App, I have different fields of Course!!
Did you try this
=DATE(FIRSTSORTEDVALUE(Datefield,-MinVal))
here is how it looks on my App, I have different fields of Course!!
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
Add the DISTINCT keyword in the FirstSortedValue() function
FirstSortedValue(DISTINCT ...
-Rob
See Rob's answer below, can you give out the points please!
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
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
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))