Hello,
I have two tables: (1) Product and (2) Storage
In my Product table I have a virtual column that automatically calculates the product suggested storage position based on a calculation (SuggestedPosition). For example: 40 (from 0 to 100).
In the Storage table I have a Position column. For example: 10, 30, 60, 100, 120, etc.
I'm looking for a way to identify which would be the closest suggested storage by comparing: (a) Product[SuggestedPosition] vs (b) Each row in the Storage[Position] column and get the minimum/closest one.
For example:
ABS(40-10)=30, ABS(40-30)=10 (winner) , ABS(40-60)=20
So far I've tried:
ANY(SELECT(Storage[id],
(
MIN(SELECT(ABS(Storage[Position]-[SuggestedPosition]),FALSE))
),
TRUE))
but with no luck.
Thanks for any tip!
Solved! Go to Solution.
You can either:
IF(
ABS(
[Position] -
MAX( SELECT(Storage[Position], [Position] <= [suggestedPosition]) )
) <
ABS(
[Position] -
MIN( SELECT(Storage[Position], [Position] > [suggestedPosition]) )
),
MAX( SELECT(Storage[Position], [Position] <= [suggestedPosition]) ),
MIN( SELECT(Storage[Position], [Position] > [suggestedPosition]) )
)
Thanks Joseph!
I have a small question.
Is it possible to update/modify the elements of a LIST?
For example: LIST(4, 3, 2) => LIST(4+1, 3+1, 2+1) => LIST(5, 4, 3)?
Yes, putting the list elements in rows.
Like literally putting them in rows inside the spreadsheet? I'm wondering if its possible with a expression.
You can either:
User | Count |
---|---|
35 | |
30 | |
30 | |
20 | |
18 |