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

# Need help with sorting, ranking, and a bin

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-

• ###### 1. Re: Need help with sorting, ranking, and a bin

Ann,

I'm not quite sure if I got there, but it felt like the issue was trying to

get the [Bin of sales] into the Addressing for the Index.

I created an LOD  that was just

{ FIXED [Item]:[Bin of sales]}

and it seemed like it could be used as a Dimension that could be put

Not sure if LOD will work for your datasource.

• ###### 2. Re: Need help with sorting, ranking, and a bin

Swaroop,

You are the master!  That worked perfectly - I had been trying some LOD calculations, but I didn't think about putting the bin calculation as the second half.

Thanks so much!

-A-

• ###### 3. Re: Need help with sorting, ranking, and a bin

Ann,