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

Accepted answers

DavidSmith

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

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

All comments

DavidSmith

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

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

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

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

Hi Darlyn,

Yes, David's suggestion makes sense.

Thanks

Andre

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…