1 2 3 4 5 Previous Next 95 Replies Latest reply on Apr 28, 2020 6:27 PM by Benjamin Marin Go to original post Branched to a new discussion.
      • 30. Re: Top N, with everyone else as "Other"
        Mike Hyde


        Thank you for the detailed response.  We were guessing that the issue is related to operations order, and sensing a conflict with the calculated field, but couldn't find it documented anywhere.  Your explanation is the best I've seen yet.


        To Tableau folks - would be great if we could find out a way to perform this.  The frustrating thing with the missing grand totals is that all the numbers are right there on the screen - I can add them up myself with a pen and paper, but tableau refuses under certain circumstances.


        Seems logical to me that Grand totals could calculate last, and without going back to the data source - just add the numbers in the table itself, more like a table calculation.



        Anyway, thanks for all the other tips in this thread - some very cool ideas here.



        • 31. Re: Top N, with everyone else as "Other"
          Chris Fisher

          Hi there all, first post here so apologise if this is something already discussed. I have understood the use of ranking to group my data into Top x and other. My question is that if I am using a heirarchy for products for example L1: Suitcases, L2: Roll Along, then item description, would it be possible to have the grouping of the results occur each time you expand down the heirarchy. So I expand TOP 10 at L1, this returns TOP 10 and other at L2 and so on.


          Interested in your thoughts.

          • 32. Re: Top N, with everyone else as "Other"
            Joe Mako



            I am not sure I understand what you are looking for, can you please provide a sample data, and describe the interaction and result that you expect?

            • 33. Re: Top N, with everyone else as "Other"
              Matthew Feliciano

              I've had great sucess creating Top N lists, in fact I created an internal document to help others in our organization create their own Top N lists. (Giving proper credit and links to this thead and the blog posts)


              My question is regarding grouped fields. (We are still on v6.0 but hope to upgrade soon). When I create my calculated field for 'Customer' using this formula

              IF [In top n?]

              THEN ATTR([Customer Name])

              ELSE "Other"



              I want to replace the 'Customer Name' with a group but my group is not in my list of fields. Is there any way to do this?


              Thank you,


              • 34. Re: Top N, with everyone else as "Other"
                Joe Mako

                That is a limitation of the Ad Hoc Group field type. Ad Hoc Groups cannot be referenced in a calculated field's formula. You will need to recreate your group as a calculated field, you can sue a CASE statement to accomplish this like



                CASE [Code]
                 WHEN "A123" THEN "A"
                WHEN "A456" THEN "A"
                WHEN "754 B" THEN "B"
                WHEN "stuffBstuff" THEN "B"


                Then this calc field can be referenced by other calc fields.

                • 35. Re: Top N, with everyone else as "Other"
                  Matthew Feliciano

                  Thanks Joe! This is going to be a huge CASE statement.

                  • 36. Re: Top N, with everyone else as "Other"
                    Richard Leeke

                    Possibly you could use data blending with a spreadsheet that holds the group mapping?  (I think that should work but would need to try it to be sure.)  Or add a lookup table to your datasource?

                    • 37. Re: Top N, with everyone else as "Other"
                      Matthew Feliciano

                      Richard - that's a good idea. I'll try that, then I can make a use case for moving this grouping in to our MDM DB where it should be.

                      • 38. Re: Top N, with everyone else as "Other"
                        Joe Mako

                        It would not work across a data blend, because data pulled across a data blend must be aggregated, and therefore not a dimension that can be used for addressing (ie attached to), and can only be used for partitioning if not ignored in table calculations.


                        In this situation, you would need to use the result of this for the compute using and you cannot use an aggregated value for compute using, only for partitioning if you set it so.


                        It would work if you did the join before Tableau, or at the data connection, but this is only viable when you can add a table to your data source.


                        If you are using an extract, then you can materialize this CASE statement by optimizing the extract.

                        • 39. Re: Top N, with everyone else as "Other"
                          Matthew Feliciano

                          I happen to have the advantage of being able to do it at the data source. I can put a CASE statement there.


                          Thanks for all the help guys!


                          Sorry to the others who aren't able to do this at the data source.

                          • 40. Re: Top N, with everyone else as "Other"
                            guest contributor

                            Joe, Is it possible to change the formula to do a Count Distinct for a Top n? 

                            • 41. Re: Top N, with everyone else as "Other"
                              Joe Mako



                              I do not understand your question. Can you please provide a sample data set that represents your situation, and detail what you would expect for a result and what interaction you would like to allow for?

                              • 42. Re: Top N, with everyone else as "Other"
                                guest contributor

                                You created an example 'Top n RL with condense option' where the user can see the Top n in a given category.  For instance, we have orders that were planned for a installation in a month but the installation  didn't occur so the order is planned in the different month.  I only want to count that order once because the order may move several times before the actual installation occur.  When I look at the my raw data, the same order number appears multiple times but I only want to count it once.  Using your example I would like to count distinct the order once.


                                Here is an example:


                                6100048944                  Everett Jones              May 01, 2011

                                6100048944                  Everett Jones              June 01, 2011

                                6100048944                  Everett Jones              July 01, 2011


                                When I count the # of orders for Everett Jones it should be ONE  even if there are three different lines for the order 6100048944. 

                                • 43. Re: Top N, with everyone else as "Other"
                                  Joe Mako

                                  This thread is old, and I do not believe that the old solutions offered here are optimal.


                                  A better method in my opinion of a dynamic top n is located at Re: Top n vs the rest


                                  as for these latest questions from Lizz45ie and Matthew, in order for me to provide assistance, I need at least a sample data set that represents your situation. I am sorry, but three records does not represent a situation. If I am to provide assistance on dealing with data, I need data, not a description of data. This is mainly because the descriptions of data leave out details that may not seem important enough to mention in a description. I would prefer a packaged workbook because table calcs are not defined by just their formula, but what dimensions are in use on the worksheet and the compute using settings and many other factors.


                                  Please provide a packaged workbook so I can understand your situation and provide assistance.


                                  And it is okay to start a new thread. This thread is old, mixed, and out dated.

                                  • 44. Re: Top N, with everyone else as "Other"
                                    Matthew Feliciano

                                    Thanks Joe. I'd love to send you a packaged workbook but am limited by HIPPA rules, my data contains protected health information so unless I deidentify all the data in the workbook I could find myself in court, subject to a fine for me and my organization as well as possible jail time.


                                    As for my posts on this 'old' thread, I'm waiting for our 6.1 upgrade next week and am unable to opne the newer examples posted.


                                    Thanks again,


                                    1 2 3 4 5 Previous Next