2 Replies Latest reply on May 3, 2013 6:33 AM by Harry Alverson

    Top 10 (n) of the bottom 20% (if greater than $100k)

    Harry Alverson

      I've been scouring the help board for pre-existing answer but can't seem to find one. The precise words to accurately describe this issue are also eluding me (interim ranking?)

       

      I have a data set with business partner revenues by city. (image below) I've managed to use various table calcs (running sums and index) to isolate the top performers representing 80% of revenues vs the bottom performers representing remaining 20% of revenues.

       

      I am now looking to create a view that isolates the "top of the bottom" performers... and only have them appear if their revenues exceed a certain dollar threshold. Since this info feeds through to a dashboard, and since different cities have different numbers of partners, the ability to do this in a dynamic automated way is proving too complex for me to solve on my own.

       

      I feel certain the right combination of FIRST(), LAST(), or SIZE() or some other table cal should be able to do the trick. I just don't understand them well enough to ferret out the answer. I'm not beholden to any one solution, but here's a brief description of what I have in place so far:

       

      To ascertain bottom 20% status, I have two calculations, the second draws on the first to create a true/false:

      1)   "Ptr 2012 Running Sum" = RUNNING_SUM(SUM([Revenues])) / TOTAL (SUM([Revenues]))

      2)   "Is Top 80%" =  [Ptr 2012 Running Sum]<.80

       

      I threw the 2nd one into my filters and selected "false"

       

      I then isolated the portion of the bottom 20% with over $100k revenues using another table calc:

       

      LOOKUP(sum([Revenues]),0)

       

      put this into filters and select "at least" 100k

       

      However, I cannot figure out how to take the remaining info and systematically isolate just the top 10 partners within this criteria.

      • Using a top 10 filter for revenue on the "partner name" dimension doesn't work because it preempts the table calculations.
      • And since the starting rank/number of both the first and last partners in the "bottom 20%" set changes between cities, I can't figure out how to nail down something that is dynamically referential to work with.

       

      Please Help!

       

      Bottom 20% partners and their revenues in City A: (top 80% partners, and bottom 20% ptrs with revenues below $100k already filtered out...)

        • 1. Re: Top 10 (n) of the bottom 20% (if greater than $100k)
          Jim Wahl

          Hi Harry,

           

          There really should be an easier way to do this, but building on what I think you've done above, I might add another calculated field that creates an index for the bottom values. Then you can filter on this index.

           

          Count of bottom values =

          IF [Is Top 80%] THEN 0 ELSE 1 + PREVIOUS_VALUE(1) END

           

          This is a nested table calculation, since [Is Top 80%] is based on the [Ptr 2012 Running Sum] table calc. After adding this field to the view---which I'd do first to test that it's working---make sure you verify the [Ptr 2012 Running Sum] table calc's compute using by right-clicking on the pill > Edit Table Calculation > in the upper pull-down box for "Calculated Field".

           

          Now Count of bottom values should be 1 for the first "bottom value" and then 2, 3, 4, ... and you can add a filter for the top 10. ...

           

          Jim

          • 2. Re: Top 10 (n) of the bottom 20% (if greater than $100k)
            Harry Alverson

            Jim, thanks for the reply. It worked!! Thanks for the insight.