I am trying to create a calculation that returns the difference in "Order" values given specific values of [Fruit].
Start at any row, say row 2. I want to create a calculated field that finds the distance to the nearest row that has each of the different fruits.
So, for row 2:
Distance to nearest row where [Fruit] = orange: 12-2 = 10
Distance to nearest row where [Fruit] = banana: 6-2 = 4
Distance to nearest row where [Fruit] = strawberry: 19-2 = 17
Distance to nearest row where [Fruit] = apple: 2-2 = 0
Any help would be appreciated. Thanks!
Jana, Vinnie,
Sorry for jumping in.
Thought I'd throw out a suggestion, though it may not be feasible for your full data.
It involves joining the datasource to itself and getting all the combinations of every row
to every other row. This join is done on a calculated field of "1" for each version of the source.
From the joined set, a distance is calculated between the order number on one copy (Sheet1)
and the order number on the second copy (Sheet11):
[OrangeDistance]:
IF [Fruit (Sheet11)]="orange" THEN ABS( [Order] - [Order (Sheet11) ] ) END
Then the Order of the nearest orange [OrangeNearestLOD] is:
{ FIXED [Order]:MIN(
IF [OrangeDistance]={FIXED [Order]:MIN([OrangeDistance])}
THEN [Order (Sheet11)]
END ) }
Basically it's finding the minimum distance, returning the order number of that row,
fixing that value to every row of the original Order number.
Then the distance to the nearest orange can be found with.
ABS( [Order] - [OrangeNearestLOD] )
There are probably some further steps necessary to deal with ties.
