1 2 Previous Next 23 Replies Latest reply on Jul 11, 2018 3:28 AM by Gitakshee Satija

    Select and view Top N and Bottom N on Same Worksheet

    christophershanahan0

      TCC 2013 was awesome and learn a lot to apply.  However, I am a bit stuck on parameters.  I am created a stock price change view based on time period (one week, one, month, YTD, etc.) and want to give viewers the option to filter by time period they want to see as well as the the Top N and Bottom N they select.  I got the parameters for Time Period to work, and got the Top N parameter.  But I am stuck on the Bottom end parameter.  I know I am supposed to create a set for Top N and Bottom N, then combine the sets, but I can't figure out how to make Top N paramter a set.  See the attached work book.

       

      Any help would be appreciated!

        • 1. Re: Select and view Top N and Bottom N on Same Worksheet
          Jim Wahl

          Hi Christopher,

           

          You can accomplish this Top N / Bottom M a couple of different ways: one is to use a table calc filter using INDEX() and SIZE(); another is with sets.

           

          It looks like you started down the first path with your calculated field Top N =

          [Index]<=[Parameters].[Top N]      // where [Index] == INDEX()
          
          

           

          If you add another parameter for Bottom N, you could then modify this formula to get Top and Bottom =

          [Index] <= [Parameters].[Top N]                
          OR
          (SIZE() - [Index]) < [Botton N]
          
          

           

          Both of these fields should be set to Compute Using > Name when added to the worksheet. In this case SIZE() returns the total number of members of the domain (the number of names / stocks).

           

          These calculated fields are called "table calc filters" because they contain table calc functions INDEX() and SIZE(). Unlike regular filters, which Tableau can apply at the data source, table calc filtering is done inside of Tableau. All of the members of the Name dimension are retrieved from the data source, partitioned, sorted and then filtered. This is often a disadvantage when dealing with large data sets, but it can also be an advantage. In the below chart, for example, you can still use INDEX() to show the rank of the bottom names to show viewers how many stocks / names are in the dataset.

          2013-10-07 09-48-58.png

           

          Using Sets

          Using sets is probably the preferred solution to the Top / Bottom problem. One reason is that unlike table calc filters, members of a set are computed in the data source and, therefore, should be much more efficient---although it won't make a much of a difference with a few hundred data points.

           

          Sets confused me until I began to think of them as "reuseable filters." Today I almost always create a set from a filter. In your example, I started with Name on the Rows shelf and Parameter Calc on the Columns shelf. Then I Ctrl-clicked Name and dragged it to the Filter shelf and selected Top N by Parameter Calc.

          2013-10-07 09-34-42.png

           

          To convert this filter to a set, click on Name in the Filter shelf and select Create Set. I called it Top N by Parameter Calc.

           

          Now that you have one set, it's easy to create a second set for the bottom n. Click Top N by Parameter Calc, select Duplicate (a shortcut I like is to right-click > D > enter). Then click on the duplicate parameter and select Edit (or right-click > E). Change the name and change Top to Bottom and the parameter to the Bottom N parameter.

           

          Now you have two sets, one for the top N and one for the bottom N. Ctrl-click both sets > right-click > Create Combined Set and select All Members in Both Sets.

          2013-10-07 09-45-32.png

           

          Now you can drag this new set to the Filter shelf, replacing the original Name filter. And you should see the same view that you had in the the table calc version:

          2013-10-07 09-48-41.png

          Jim

          5 of 5 people found this helpful
          • 2. Re: Select and view Top N and Bottom N on Same Worksheet
            christophershanahan0

            Thank you so much and I certainly appreciate your taking the time to show me the more the more efficient method and explain the difference between that and the path I was going down by not using set.

            • 3. Re: Select and view Top N and Bottom N on Same Worksheet
              Jose Silva

              Hi Jim,

               

              Hi, I am trying to show the top and bottom products according to sales, but the problems is, when I try to do this by sets it seems that the values I obtained are wrong.

               

              Obtaining the Top N products is easy, just using a Rank and filtering this to show the top 5 (For example), but when I do this using "sets" the result it does not show the correct ranking. What am I doing wrong?, can you help me please?. I would like to find the bottom values as well, but I do not trust in this method anymore.

               

              Top 5 (using Rank)

              Top 5 by Rank.png

              Top 5 (using sets), there it can be seen that the values are different

              Top 5 using sets.png

               

              Many Thanks!

              José

              • 4. Re: Select and view Top N and Bottom N on Same Worksheet
                Mahfooj Khan

                Hi,

                 

                Drag the created set into filters shelf.

                 

                Mahfooj

                • 5. Re: Select and view Top N and Bottom N on Same Worksheet
                  Jose Silva

                  Hi Mahfooj,

                   

                  Thank you for your reply, I did it. But, since the moment I establish the condition (Top 5) in the filter, it show the wrong values.

                   

                  Jose

                  • 6. Re: Select and view Top N and Bottom N on Same Worksheet
                    Mahfooj Khan

                    Hi,

                    Share your expected output or the workbook.

                     

                    Mahfooj

                    • 7. Re: Select and view Top N and Bottom N on Same Worksheet
                      Jose Silva

                      Hi,

                       

                      In the Workbook you will find in the first tab the expected output, and in the second tab the result obtained using the set. You will see that it is different

                       

                      Expected and "correct" top 5

                      Top 5 by Rank.png

                       

                      Top 5 obtained using "set" defined. Please, look that the top values are wrong

                      Top 5 using sets.png

                       

                      Thanks

                      Jose

                      • 8. Re: Select and view Top N and Bottom N on Same Worksheet
                        Mahfooj Khan

                        Hi,

                         

                        You can simply create a set like this.

                        Right click on the field on which you want to create a set.

                         

                        And then see the output once again.

                        You can use parameter also in place of 5 lets say Top N.

                        Top N will be your integer parameter.

                         

                        Mahfooj

                        • 9. Re: Select and view Top N and Bottom N on Same Worksheet
                          Jose Silva

                          Hi,

                           

                          I am sorry Mahfooj, I know how to create a set, and I did it in that way, but the problem is that the set created does not work, It is not showing the correct Top (or bottom) values.

                           

                          Many thanks

                          José

                          • 10. Re: Select and view Top N and Bottom N on Same Worksheet
                            Mahfooj Khan

                            Okay!

                            You can try one thing. Once put context filter on Product ID in filter self then check.

                             

                            Mahfooj

                            • 11. Re: Select and view Top N and Bottom N on Same Worksheet
                              Jose Silva

                              Thanks Mahfooj,

                               

                              I have found an information very useful in another blog related to this. Thanks for your help

                              • 12. Re: Select and view Top N and Bottom N on Same Worksheet
                                Aalok Jain

                                I am trying to achieve Top N for

                                1. Two dimentions that are concatenated e.g. [A] + "-" + [B]

                                2. Combined Fields

                                 

                                I use the Top N parameter in filter option for combined field however output seems to be different when top 5 or top 10 show in comparison to using index().

                                 

                                Index() does not allow me to filter for some reason.

                                 

                                Just one of those days where i just cant think...

                                 

                                Please help.

                                • 13. Re: Select and view Top N and Bottom N on Same Worksheet
                                  ammi abba

                                  Hi Jim

                                  Great Post

                                  but i have one concern like in your chart and same thing for me also..

                                  if we look your chart for value (2.1)  showing 2 names like

                                   

                                  Name                     Rank          Value

                                  citi group                   6                 2.1

                                  cignature Bank          7                 2.1

                                   

                                  as per my concern Rank should be same for both? suppose   if i select Top 6 the answer should be (Citi group )

                                  what about cignature Bank ?

                                  • 14. Re: Select and view Top N and Bottom N on Same Worksheet
                                    T G

                                    Hello Ammi,

                                     

                                    That 6 and 7 are indexs not Rank. When you use Rank() it will give the same number.

                                     

                                    TG

                                    1 2 Previous Next