4 Replies Latest reply on Feb 12, 2019 8:07 AM by Joe Oppelt

    Closest 5 Items in an Index

    Sterling Winter

      Hello,

       

      Below is an example of a table from Tableau with item names changed to various colors.

       

      I am wondering if there is a way in Tableau to select "Pink" in a menu and have the view jump to "Pink" in the table and highlight that row while displaying the 5 closest values by index on either side. The 1-10 can be ignored if needed as a second table can be used and just aligned in a dashboard with top 10 from index showing.

       

       

      IndexItemQuantity
      1Yellow1,658,377,718
      2Blue1,167,867,531
      3Black491,796,555
      4White260,673,126
      5Brown251,152,617
      6Green219,956,662
      7Orange205,763,503
      8Red105,602,026
      9Purple100,617,884
      10Grey94,297,390
      204Teal112,167
      205Cyan105,757
      206Magenta103,793
      207Lavender102,763
      208Pink92,928
      209Olive86,620
      210Lime62,964
      211Beige62,233
      212Apricot61,091
      213Maroon53,676

       

      Thanks!

        • 1. Re: Closest 5 Items in an Index
          Joe Oppelt

          Set up the "menu" using a parameter.


          From there you can find the index for the selected value and grab from -2 through +2 based on that index value.

           

          Upload a sample workbook and we can work it together.

          • 2. Re: Closest 5 Items in an Index
            Sterling Winter

            Hello Joe,

             

            I had to create a fake data set but the concept is still the same just with 19 junk foods and randomized quantities.

            • 3. Re: Closest 5 Items in an Index
              Sterling Winter

              Also, I am using Tableau 10.5 just for your reference.

              • 4. Re: Closest 5 Items in an Index
                Joe Oppelt

                In the attached I made a parameter ([Item List]) that I populated with the values from [Item].  The user can select an item in the parameter.

                 

                Next I created a calc to find the index value for the selected item.  See [Which item index ...].  I displayed that on your original sheet.  You will notice that it only appears on the row of the item selected.

                 

                I made another calc to propagate that to all rows.  See [Put index...].  I left this as two separate calcs to compartmentalize the steps, but you can nest these by wrapping the syntax in [Which item...] with the WINDOW_MAX( ) function, and it will all get done in one calc.

                 

                So as you select a value from the param, you'll see those numbers change accordingly.

                 

                Go to the copy of your sheet that I made for the next step.


                Here I made a calc to filter so that we get 5 rows displayed (or up to 5 if the user selects a value where index = 1 or 2.)  This just grabs the range you want to see.  For the record, the rest of the rows are still in the underlying table (so that the index function still sees "Chocolate" as index = 6).  When you use a table calc as a filter, it doesn't delete rows from the underlying table like a quick filter does.  It just controls what part of the table to display.  This is to allow other table calcs to function properly as you see here with the [index] table calc.

                 

                (If you needed to, you could take this a step further and make another calc to let the user choose the range to display, instead of hard-coding 2 in there.  Just a suggestion.)


                See attached.

                 

                PS:  Note that this is a very simple example.  You have a simple TABLE(down) construct on the sheet, and Tableau properly interpreted that, and defaulted all the table calcs (anything with a triangle on the right end of the pill) to walk the table as TABLE(down).  If you had a more complicated sheet, you might need to manually set the addressing of the table calcs.  If you run into that and need help, let me know.