Group option in RANK Function




I have a module with 3 or more lists, and Value and Rank line item.  I want to rank Value for each combination of items in those 3 lists.  However, I find that RANK function can only accommodate 1 list in the Group option.

How can this be done?




Best Answer

  • DavidSmith


    Maybe I'm misunderstanding, but the combination of all dimensions will only ever return a single value so there isn't anything to rank.

    What you can do is rank on each dimension and then add the rankings together to create a composite ranking

    If you are looking to rank on some of the dimensions in combination, the only way to do this would be to create a combination key and use that as the group by.

    The attached shows that option.  The Gropu By line item is a combination of Customer and Channel using the following formula: NAME(ITEM(Customers))&NAME(ITEM(Channel))




    However, it might also be worth seeing if you can create a combined hierarchy list, dimension a module by that combined list do the ranking there before passing the value back if needed

    I hope that helps



  • Hi @DavidSmith,


    Thanks for the quick response.


    Sorry for not being clear about the question.


    Let's say I have a module of 3 lists: List1, List2, List3. The module line item is 'Value'


    Items in List 1: Item 11, Item 12

    Items in List 2: Item 21, Item 22, Item 23

    Items in List 3: Product 1, Product 2, ..., Product 100


    I want to find the Top 10 products for each combination of items in List 1 and List 2


    However, the RANK function only allows one list for the 5th parameter which is the Group parameter.  If I use RANK(p1,p2,p3,p4,ITEM(List 1)), the rank will take into account all items in List 1 and List 3, creating rank from 1 to 300 (3 x 100), and vice versa if I use RANK(p1,p2,p3,p4,ITEM(List 2)), the rank created will be from 1 to 200




  • Hi Andre,


    I believe that is why David had the column " Group By" that concatenates List 1 and List 2 so you can put the contenated List 1 _ List 2 as the "Group" Parameter.


    Does this make any sense?




  • Hi Darlyn,


    Yes, David's suggestion makes sense.