7 Replies Latest reply on Mar 21, 2014 3:15 AM by Teresa Obis

    my closer N instead Top N

    Teresa Obis



      I try to find display my closer competitors instead of the leaders (or top N).


      Find attached a workwook with a simple example. In the worksheet I have the list of all products sorted by Sum(Sales) and a parameter that let me to mark my interested product. For example P2.

      In the list P2 is 4th, so I would like to see  only the closer products to the one I have choosen. For example if I would like the one closer, I would like to see the 3er, 4th and 5th.

      I only be able to determine wich is my product, but then I do not know how to select the previous and the following one.


      Please, can you help me?


      Thank you,



        • 1. Re: my closer N instead Top N
          Joshua Milligan



          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
          ATTR(Product) == [My Product] //Current
          LOOKUP(ATTR(Product), 1) == [My Product] //Next





          • 2. Re: my closer N instead Top N
            Noah Salvaterra

            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).

            window_max([Selected products])

            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?

            1 of 1 people found this helpful
            • 3. Re: my closer N instead Top N
              Matt Lutton

              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.

              1 of 1 people found this helpful
              • 4. Re: my closer N instead Top N
                Joshua Milligan



                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]))




                • 5. Re: my closer N instead Top N
                  Teresa Obis

                  Thank you,


                  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,



                  • 6. Re: my closer N instead Top N
                    Noah Salvaterra

                    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.

                    • 7. Re: my closer N instead Top N
                      Teresa Obis

                      Hi guys,


                      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