It very much depends on exactly what you mean by "close". Is it close in the ranking? (That's what I assume) or close in the list, no matter what the order?
You could expand your calculated field like this to find close products in the list. If the list is sorted by whatever metric you wish to rank, you'll have the correct answer. Alternately, you could edit the table calculation in the view and set the sort according to exactly how you want "close" to be considered.
[Selected or Close]
LOOKUP(ATTR(Product), -1) == [My Product] //Previous OR ATTR(Product) == [My Product] //Current OR LOOKUP(ATTR(Product), 1) == [My Product] //Next
You were pretty close. With your index calculation you computed where to look for the percentage of your product, but you need to make that value visible to other rows in the dataset. I did this with a window_max (though nearly any window function would do the job just as well).
I selected compute using to be product for my calculations, because I like the flexibility of moving things around, but table down works just as well in your example.
Next I need the percentage of the selected product, since I know where to look, I can go get it. Note, I replaced the percentage of sales calculation in the viz with a hard coded one to make referencing easier.
lookup([Percent of Sales],first()+[Selected Product Calc]-1)
Finally, I can compute the distance:
abs([Percent of Sales]-[Selected Percentage])
At this point if you wanted to filter to top N you could do in the usual way. If you wanted to exclude the selected product from this view you could always displace it:
if [Selected products]>0 then 1
else abs([Percent of Sales]-[Selected Percentage])
Does that do it?
Sort by Distance.twbx 78.7 KB
Here is your workbook with Joshua's solution applied. I was trying to figure this one out, and this was the approach I was looking for. Seems to work great and is very simple to implement.
Example2 JM Solution.twbx 21.8 KB
I really like your approach. I think it could be simplified slightly by making the first calculation return the percent for the selected product instead of the Index. That way you could calculate distance as abs([Percent of Sales]-window_max([Selected Product Percentage]))
Josua, you give me the rigrh answer. Thank you very much.
Matthew, thanks for put it in my worbook.
Noa, your answer is very interesting. Now I does not the one I need, but it could be useful in the future,
Joshua, you're right, that would be simpler! The index was already there so I used it.
Teresa, thanks, I'm glad you got the answer you needed.
I still have problems with the list of my closer products in the ranking.
Now a day I have done a list that show the 5 leaders, if my product is in this list (left image), but only the to 2 leaders and the two products close to the mine and my product when my product is not in the 5 leaders (middle image)
But I do not what happend when my product is just the 5th, I've got a null value. Why?(rigtht image).
Also I have problems to get the index position and the row total at the same time (left) and to make changes in the ranking when I select a month
I would like to introduce the total sales, even I use the Index variable and I would like to make an action to get the raking by month.
Is that possible?
Find attached the workbook with the example
Example2.twbx 85.0 KB