1 2 3 Previous Next 33 Replies Latest reply on Jun 11, 2015 2:25 PM by Tyler larsen

    Top n vs the rest

    Arjan Eriks

      Hi All,

      Hope you can help me. The forum and knowledge base have been a great help already on determining the top n within a category (see http://www.tableausoftware.com/support/knowledge-base/finding-top-n-within-category), but i have an additional request.

       

      I used the link above to determine the top 5 databases within my environment (IT Capacity planning). I also calculated the difference related to the month before. This feature works fine, but it would be even better if I could show the following

       

      
       #1 - Bigdatabase - 25.000
      #2 - Prettybigdatabase - 20.000
      #3 - Mediumdatabase - 18.000
      #4 - #12 - All other - 67.000
      
      
      
       This way you can see the relation of the capacity compared to the total. As i am using an Odata interface (to an OpenTSDB (Hadoop Hbase cluster)) I cannot fix anything in my SQL code because there is only an URL.
      
      Help would be highly appreciated. Thanks, Arjan
      
      
        • 1. Re: Top n vs the rest
          Arjan Eriks

          Maybe this workbook makes it more clear. I pumped the data in xlsx and included it in the workbook. Now it shows the top 3. I would like to see top 4 and 5 combined as 'other' if you catch my drift.

          • 2. Re: Top n vs the rest
            Joe Mako

            How about the attached?

             

            Lots of good stuff going on in this workbook. I would like to detail the concepts at play when I get the chance.

             

            (side note: this is not exactly the same approach used in http://www.tableausoftware.com/support/forum/topic/top-n-everyone-else-other , this attached workbook is more robust in my opinion)

            1 of 1 people found this helpful
            • 3. Re: Top n vs the rest
              Arjan Eriks

              Joe, this is pretty amazing to be honest. It will probably take me some time to redo this stuff in my real workbook. Thanks so far! This is exactly what i want!

              • 4. Re: Top n vs the rest
                Joe Mako

                Here is the breakdown of what I did:

                 

                - a Set of the two dimensions at play

                - a duplicate of the top level dimension

                - five calculated fields (could be condensed to four)

                - the most interesting stuff happening in "Display Value" and would need to be edited to fit your situation

                - "Database" is the only other formula that needs to be edited to apply

                - two parameters to control the display and calculation

                - the compute using for all table calc pills is the Set

                - the Set is ordered on the measure field

                - the "In Top n" pill is ordered so True is above False

                 

                and that is about it.

                 

                There are all sorts of other things that can be done, eg if you wanted to add in sub-totals, but modifications would need to be made to accommodate.

                • 5. Re: Top n vs the rest
                  Joe Mako

                  here it is cleaned up, condensed to four calc fields. I noticed I had left an unnecessary calc in, and removed it.

                  • 6. Re: Top n vs the rest
                    Arjan Eriks

                    Thanks again. Really appreciate the help. We are making our service reporting from xls based once a month to always real time, so I am in the middle of creating some very nice visuals based on OpenTSDB with Odata. When I am a bit further i'll show some concepts. But basically it is gathering data on every system (we have about 4000) every 5 minutes. Import this every night in an OpenTSDB database. And based on this database we will do trend analyses with Tableau. Keep you posted.

                    Oh and i probably have already one extra question, can i sum the averages of the 'other' category?

                    Cheers, Arjan

                    • 7. Re: Top n vs the rest
                      Joe Mako

                      > can i sum the averages of the 'other' category?

                      Yes, just change the formula for "Display Value" to:

                       

                       

                      IF [In Top n] OR [Show all Vendors]
                      
                       THEN AVG([Value])
                      ELSEIF INDEX()=[Select Top n]+1
                      THEN WINDOW_SUM(AVG([Value]),0,LAST())
                      END
                      


                      • 8. Re: Top n vs the rest
                        David. Lewis

                        I kind of stumbled across this... Amazing.  Very cool.  I'm busy trying to understand it.  thx Joe. 

                        One question, though--why do you need to duplicate the top level dimension?

                        • 9. Re: Top n vs the rest
                          David. Lewis

                          Joe:  I've been trying to replicate your method, but for some reason I cannot see my 'Show all Vendors' parameter selection has no effect on what is displayed -- all line items are display all the time.  I took some sample data and rebuilt your worksheet step by step, several times now, with the same result.  If you or someone else care to look at it and if you notice where I messed up, I'd appreciate it.  thx (hopefully) D Lewis

                          • 10. Re: Top n vs the rest
                            David. Lewis

                            Found the problem!  Thanks for looking at the thread, though!  d

                            • 11. Re: Top n vs the rest
                              David. Lewis

                              Hi Joe:  In case you are still reading this thread, looking closely at your workbook I see that the 'x Others' line item does not correctly show the sum of the lines.  I don't understand the equations enough to troubleshoot it, though.  Also, do you by any chance understand why your bars show the values as data labels as a default.  I cannot get them to show up on my version unless I place the 'display value' pill on the label shelf.  Just curious.

                              Also, in my version (as best I can tell it is a xerox copy of yours, with different data underneath) I do not get a bar generated for the 'x others' row.  Anyone have any thoughts as to why?  many thanks. d. lewis

                              • 12. Re: Top n vs the rest
                                Joe Mako

                                The formula for "Display Value" is averaging the quantity, not performing a sum. Would you like a sum or some other aggregation?

                                 

                                also you need to set the compute using for Display Value to the set "SubCode - Type".

                                 

                                I use the Label context menu to "Show all labels", I also turned stack marks off to get the label in the correct spot.

                                 

                                Changes are made in the attached.

                                • 13. Re: Top n vs the rest
                                  David. Lewis

                                  Thanks very much!  I hadn't noticed it was an 'AVG' calc.  !?  This is very cool, and I'm off trying to find ways to add it everywhere.  I just found the site with your video tutorials -- thanks for taking the time with them.  Regards, d. lewis

                                  • 14. Re: Top n vs the rest
                                    James Baker

                                    Excellently done.

                                    1 2 3 Previous Next