Group option in RANK Function

andre.lie
andre.lie
in Modeling

Hi,

 

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?

 

Regards,

Andre

Tagged:

Best Answer

  • DavidSmith
    DavidSmith
    Answer ✓

    @andre.lie

    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))

    2018-10-09_12-18-49.png

     

     

    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

    David

Answers

  • andre.lie
    andre.lie

    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

     

    Regards

    Andre

  • darlynyang
    darlynyang

    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?

     

    Cheers,

    Dalryn

  • andre.lie
    andre.lie

    Hi Darlyn,

     

    Yes, David's suggestion makes sense.

     

    Thanks

    Andre

Categories