12 Replies Latest reply on Jul 1, 2013 5:42 PM by Suzanne MUllen

    TOP n, by x

    Ryan Szymanski

      Hello,

       

      Researching previous comments, are struggling to apply a  'Top 10 Filter' for a given Dimension, against another Dimension.

       

      In my example, are looking to associate the Top 10 'Issuer Names' for each Given 'Portfoliio'.

      Attempting through different iterations,, it appears that the Top 10 filter is applied against my enter data set, rather then each Portfolio.

       

      Attaching a sample workbook as example, are hoping for any advice that would help order the 'Top 10' for each Portfolio rather then the Sum of all Portfolio.

       

       

      Thanks in advance, your kind advice is appreaciated.

        • 1. Re: TOP n, by x
          Joshua Milligan

          Ryan,

           

          It looks like you had tried several things and gotten close!

           

          The trick to getting the Top N for a category is to use the Index() table calculation -- and make sure it is correctly partitioned, addressed, and ordered.

           

          You'll need to use the Advanced settings and put your partition field (Portfolio Name) and your Address field (Issuer Name) in the correct order.  Make sure to Order Along the field you are ranking (I think you were using Market Value (%)).

           

          rank 2.PNG

           

          Then, you'll want to set "At the level"  This is your addressing field (Top N ________.  In this case Issuer Name.)

          Then set "Restarting Every".  This is your partitioning field (Portfolio Name).

          rank.png

          Once your field is setup, you should be able to get the result you want.  Top 10 Issuers by Portfolio.

          Hope that helps!

           

           

          Joshua

           

           

          Top 10.PNG

          • 2. Re: TOP n, by x
            Ryan Szymanski

            Hi Josh,

             

            For all the stuggles, this is unreal !  And jugging by the time of your response should have stayed up.

             

            ... Retooled this morning, followed your instructions and found the 'Advanced Settting' and scheduled partitions in accordance.

             

            Might seem small to you, though Managers at the office are going to be Wowed by the site of their Top 10's in tomorrows reports.

             

            Really cool trick, thanks again,

             

            Ryan

            • 3. Re: TOP n, by x
              Chandra Shekhar Jain

              Josh

               

              Nicely Done , I was searching for this solution too.

               

              Adding to the complexity is there any way to get Top x% of Issuers instead of Top 10.

               

              Chandra

              • 4. Re: TOP n, by x
                Joshua Milligan

                Chandra,

                 

                Sure!  It would be the same concept, except, instead of using Index() it would use a table calculation that would calculate the percent, something like:

                 

                SUM([Sales]) / TOTAL(SUM([Sales]))

                 

                Using the same partitioning and addressing described above.  You would be able to use that as your filter and keep anything above a certain threshold.

                 

                 

                 

                Also, just a note for my above example, I used Index() which works, but there are some intricacies to be aware of.  Index() will increment even when the values are the same.  So, if you'll get:

                 

                ItemValueIndex()
                A10001
                B5002
                C5003
                D14

                 

                B and C were tied, but if you did the top 2 you would only get A and B.  There are ways around this, such as using a table calculation other than Index().  The code would look something like:

                 

                IF LOOKUP(ATTR(Item), -1) = LOOKUP(ATTR(Item), 0)

                THEN PREVIOUS_VALUE(SUM(0))

                ELSE  PREVIOUS_VALUE(SUM(0)) + 1

                END

                 

                That would give you

                 

                ItemValueCustomTableCalc
                A10001
                B5002
                C5002
                D13

                 

                Then you'll get the tie when you filter to the top 2.  (But if you filter to Top 3, you'll get 4 items!)  If you really wanted to get fancy, you could come up with something that would give you:

                 

                ItemValueCustomTableCalc
                A10001
                B5002
                C5002
                D14

                 

                It would probably take a couple of different table calculations andsome fancy logic.

                 

                Joshua

                • 5. Re: TOP n, by x
                  Jonathan Drummey

                  James Baker came up with a variety of rank calcs awhile back, here are some links:

                   

                  Joanthan

                  • 6. Re: TOP n, by x
                    Chandra Shekhar Jain

                    Josh

                     

                    Sum(Sales)/Total(Sum(Sales)) give you a % of Sales of that particular Issuer.

                    In this case will we be able to filter Issuers who cumulatively are in the Top 20% of All Issuers.  (Not just someone who has a 20% Share)

                     

                     

                    Chandra 

                    • 7. Re: TOP n, by x
                      Joshua Milligan

                      Jonathan,

                       

                      Thank you very much for posting those links! 

                       

                      It'd be great to have a place where that kind of knowledge was easily accessible -- maybe some "Forum Hall of Fame" or something like that.  I feel like I'm often re-inventing what has been discovered before.  That's not a bad thing as there's a lot to learn in that way, but knowing where to find things would be nice too.

                       

                      Regards,

                      Joshua

                      • 8. Re: TOP n, by x
                        Joshua Milligan

                        Chandra,

                         

                        I think it will depend on how you are computing the table calculation.  I can use the same calculation and get very different results depending on how I partition and address the calc.

                         

                        Regards,

                        Joshua

                         

                        % of total.PNG

                        • 9. Re: TOP n, by x
                          Chandra Shekhar Jain

                          Joshua

                           

                          I second, third , fourth ,..............that.

                           

                          Chandra

                          • 10. Re: TOP n, by x
                            Chandra Shekhar Jain

                            Excellent Example.

                             

                            Modifying my query to suit this example.

                             

                            In Furniture Category I would like Only 

                            Tables                                  36.61

                            Chairs and Chairmats             34.02

                            Book Cases ,                       15.89

                             

                            to be displayed as cumulatively these Sub Categories form the top 80%  of my Category Sales

                             

                            In Office Supplies I would like Only

                            Storage and Organization                28.52

                            Binders and Binder Accessires       27.26

                            Appliances                                    19.64 and

                            Paper                                            11.90

                            to be displayed as cumulatively these Sub Categories form the top 80%  of my Category Sales

                             

                            and Similarly for Technology (I would only Show Office Machines , Telephone and Copiers and Fax).

                             

                            I am not getting around doing showing my Subcategories that form the top 80% of my categories.

                             

                            Chandra

                            • 11. Re: TOP n, by x
                              Jonathan Drummey

                              Hi Joshua,

                               

                              That has been a continuing issue. Part of why I published my (ever expanding) set of Tableau notes as a wiki at http://drawingwithnumbers.artisart.org/wiki/tableau was to help highlight some of this content and signal-boost it in the search engines. Shawn was doing a similar thing with his weekly posts as well.

                               

                              Back in the old forums there was a section called "Tableau Calculation Reference Library" and the posts I linked to from James were part of that. It's coming back sometime soon, Dustin & Tracy & James are working on that now. I also know that the Knowledge Base is going to get more material this year, and with the improved search engine those should all make a difference. I don't think all that quite gets to the "hall of fame" you were describing, and it's progress!

                               

                              Jonathan

                              • 12. Re: TOP n, by x
                                Suzanne MUllen

                                Hi Joshua,

                                 

                                This is really helpful. One last piece I am struggling with --- how can we sort the bars in descending order by Issuer?

                                 

                                Thanks in advance,

                                Suzanne