Ranking with continued sequence

Hi Community

I have a simple list containing values, but some of them have the same values. So when I rank them, the ranking is quite discontinued. For example, 1,1,3,3,3,6,6,6,6,6,6,6..... but what I want is 1,1,2,2,2,3,3,3,3,3,3,3,....... How to approach it.

Thank you.

Best, Qianru

Accepted answers

JaredDolich

@qsong1

Very common question.

@jasonblinn answers that very nicely in this post.

Unfortunately, it's not a simple use of the RANK function but with all things Anaplan, it's possible!

JaredDolich

@qsong1

Here's one way, which is probably the most expeditious and reliable, also one that can be automated but it does require you to run an action.

We'll create the following:

Transaction Module (holds your 1 dimensional module and the values you want to rank)
System Module for the transactions
A List that contains all the unique values of the value you want to rank
An action that creates the list above
A system module for the list

First, Create the transaction module. You mentioned no dimension so I assumed you meant its a transaction module with 1 dimension. In this case I chose an arbitrary unique identifier using a sequential number.

Note: you can see the last line item provides the ranking you want!

Secondly we create a system module for the transactions. This follows the DISCO methodology - calculate once refer often. Make sure you create a saved view that only contains the list items. There should be no aggregate values in the list or you will get errors in the next step.

Next, we create the List of unique values using the system module we created above. Create an import action to do this and save/rename your action because you will need to run this action everytime you want to reroll the rank. You can add this action to a dashboard to make it easy to access.

Now that we have a list of unique values we can rank them in our system module for the list you created above. Again, use the DISCO methodology and set all summaries to "none".

As you can see the "Rank" line item contains the right value we want. Last step is to add a line item to our transaction module (or system module) that contains the final rank.

All comments

JaredDolich

@qsong1

Very common question.

@jasonblinn answers that very nicely in this post.

Unfortunately, it's not a simple use of the RANK function but with all things Anaplan, it's possible!

qsong1

@JaredDolich Thank you for your answer. I tried to use the suggestion, but I found ISFIRSTOCCURRENCE does not work for mi case, as I do not have a hierarchic dimension.

Numbers calculated	Simple Anaplan Ranking	Final Ranking
6	1	1
3	3	2
6	1	1
3	3	2

You see I do not have any dimension here. So I do not know how to put the part of dimension: ISFIRSTOCCURRENCE(Simple Anaplan Ranking, ?). I appreciate any assistance.

Best, @qsong1

JaredDolich

@qsong1

Here's one way, which is probably the most expeditious and reliable, also one that can be automated but it does require you to run an action.

We'll create the following:

Transaction Module (holds your 1 dimensional module and the values you want to rank)
System Module for the transactions
A List that contains all the unique values of the value you want to rank
An action that creates the list above
A system module for the list

Note: you can see the last line item provides the ranking you want!

Now that we have a list of unique values we can rank them in our system module for the list you created above. Again, use the DISCO methodology and set all summaries to "none".

As you can see the "Rank" line item contains the right value we want. Last step is to add a line item to our transaction module (or system module) that contains the final rank.

qsong1

Thank you @JaredDolich . That is awesome.

JaredDolich

You bet @qsong1 !

Let me know if this idea works for you!

There are always more than one way to solve these types of problems in Anaplan.

Quick Links

Can you help?

Unanswered questions

import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…
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.…