6 Replies Latest reply on Mar 1, 2016 12:36 PM by anthony.pierzchalski

    Top 10 by dimension

    anthony.pierzchalski

      Hi,

       

      I'm trying to get the top 10 (N) Clients per dimension when choosing a single item.  On the attached workbook I'm trying to get the top 10 Client for the Company Astro when it's chosen in the Company filter.  However all I'm getting is the Clients for Astro that are part of the top 10 in total, not the top 10 for Astro.  Please help and let me know what I'm doing wrong.

       

      Thanks

        • 1. Re: Top 10 by dimension
          anthony.pierzchalski

          Sorry, one more thing I thought of.  I also need the year added as a column as updated in the attached workbook and that throws another wrench into what I'm looking for because I'm looking for the Top N based on the year.  It appears it's giving me the Top N based on total in the data and not the particular year I'm choosing.

           

          Thanks

          • 2. Re: Top 10 by dimension
            Martin Estrada

            Anthony,

             

            If you are looking to view the Top N for a specific company and year you can create a calculated field based off an index:

             

            INDEX () <= Top 10 and place it on the filter shelf running along the Client dimension.

             

            I attached a workbook. Is this what you are looking for?

             

            Thanks,

            Marty

            1 of 1 people found this helpful
            • 3. Re: Top 10 by dimension
              anthony.pierzchalski

              Hi Marty,

               

              Thanks for the workbook.  It's close, but not quite there because when indexing it looks like I can't get Top N for all companies when all companies are selected.  Plus when I add a total to the column, it's giving me more than what the Top N sum to.

               

              Thanks

              • 4. Re: Top 10 by dimension
                Ben Page

                Hi Anthony,

                 

                Try this-- I've created one field that you can use as your filter:

                You need to do an advanced table calc and address both Company and Client, and restart every company:

                I don't think you're going to get what you're looking for if you allow a user to look at multiple years of data at once (you can't sort data by two years at once). So, I've set your year filter to "single value dropdown." My work is on the "Year Included Top 10 2015" Tab. Let me know if this is what you're looking for.

                 

                Thanks,

                Ben

                 

                **UPDATE:

                 

                I forgot to actually sort the data. I've created another field to do this:

                RANK(SUM([Billed Revenue]))

                 

                And used the same advanced table calc as above.

                1 of 1 people found this helpful
                • 5. Re: Top 10 by dimension
                  anthony.pierzchalski

                  Thanks Ben, but still not quite what I'm looking for.  I'm looking to be able to get the Top N of both companies when both companies are selected.  Then get the Top N of each individual company when only 1 of the companies is selected.  Also, when I add a column total it's giving me the total for the company and not just the top N being shown in the window.

                  • 6. Re: Top 10 by dimension
                    anthony.pierzchalski

                    Ben, I forgot to mention about the years.  I don't want to sort on both years, I just want to get the top N for 2015 while showing their historical performance at the same time.  Then if needed, I'd like them to be able to get the top N for 2014 to see how they did in 2015.  I'm not asking for to much am I?