I’m trying to order a SELECT list by another column (date) but I’m obviously doing it wrong.
This expression gives me the first thing in the list: =TOP(SELECT(MOULDING BOX MOVEMENTS[MOVED TO],[Product Code on Overstock]=[_THISROW].[Product Code on Overstock]),1)
But I want the first thing based in the date: Have tried this =TOP(ORDERBY(SELECT(MOULDING BOX MOVEMENTS[MOVED TO],[Product Code on Overstock]=[_THISROW].[Product Code on Overstock]),[Date Received]),1)
But it says ORDERBY has invalid inputs
Am I totally wrong, or close?
Solved! Go to Solution.
This should get you the desired row:
MINROW(“MOULDING BOX MOVEMENTS”, “Date Received”, ([Product Code on Overstock] = [_THISROW].[Product Code on Overstock]))
If you use that as the app formula for a virtual column, you could then get the MOVED TO column value via the virtual column:
[My VirtCol].[MOVED TO]
Would that be adequate?
@Simon_Bailey is [MOVED TO] column your key?
ORDERBY() expects a list of KEY column values as the first argument. I recommend using FILTER()–which returns a list of KEY column values–rather than SELECT() when using ORDERBY(). For instance:
=TOP(ORDERBY(FILTER(“MOULDING BOX MOVEMENTS”, [Product Code on Overstock] = [_THISROW].[Product Code on Overstock]), [Date Received]), 1)
TOP() returns a list, even if just a list of one item. If you just want the one item rather than a list of one item, use INDEX() instead of TOP().
MOVED TO isn’t the key column and neither is the Date column I want to order by.
INDEX might work, but i want to return the Moved To column based on the 1st Date Column
So you want the MOVED TO column value of the row with the matching Product Code on Overstock with the oldest Date Received value?
Yep, that’s exactly what I want
This should get you the desired row:
MINROW(“MOULDING BOX MOVEMENTS”, “Date Received”, ([Product Code on Overstock] = [_THISROW].[Product Code on Overstock]))
If you use that as the app formula for a virtual column, you could then get the MOVED TO column value via the virtual column:
[My VirtCol].[MOVED TO]
Would that be adequate?
+Steve Coile Thanks Steve. That’s done it
User | Count |
---|---|
41 | |
36 | |
30 | |
23 | |
16 |