3 Replies Latest reply on Aug 5, 2016 8:30 AM by swaroop.gantela

    Need help with sorting, ranking, and a bin

    ann.stolzman

      Hi everyone,

       

      I have a task to display some data the following way:   I need to show, by user, the number of cases and total sales for that user, and to rank those users by the sum of sales.  I also have a parameter for users to select how many of the top items they want to see (so they can see the top 10, or top 200, by sum of sales).   In this case I'm using Item to stand  in for user names, the count of items as the count of cases, and the sum of sales as the sum of sales.  Top, in this workbook is an index calculation for ranking the users based on sum of sales.

       

      Now, there's a complication to this - I need to have a categorization of sales by the sum of sales.  So, based on the sum of sales, I've created a calculation that "bins" the users based on the sum of sales.  Once I create that, the ranking calculation goes out the window.  And the parameter goes wonky.

       

      So, based on the workbook, I'm looking for the ranking in small to be 1,2,3,4,etc., then it restarts with medium 1,2,3,4, etc., based on the sum of sales for that user.  On top of that, the parameter needs to work for the ranking that is in each bin, not the table overall (so, when I select 5, I see the top 5 in each bin, not the top 5 for the table overall)

       

      I've created a workbook in order to try and work this out, but I can't quite get there and could use some help.  Is it possible to do this?

       

      Any ideas?

       

      Thanks,

      -Ann-