Highlighted
Certified Master Anaplanner

Group option in RANK Function

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

4 REPLIES 4
Highlighted
Master Anaplanner/Community Boss

Re: Group option in RANK Function

@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

Highlighted
Certified Master Anaplanner

Re: Group option in RANK Function

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

Highlighted
New Contributor

Re: Group option in RANK Function

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

Certified Master Anaplanner

Re: Group option in RANK Function

Hi Darlyn,

 

Yes, David's suggestion makes sense.

 

Thanks

Andre