Ranking Project - One Category at a time

gaurav_sharma-bng

Hi All,

Hope you are having a great day so far!

I have a requirement to rank projects based on specific criteria of Profits from each of them. However, they need to follow a specific order to be considered for Ranking specified below-

a. Mandatory Projects should be ranked first based on their individual profits

b. Government Projects should be ranked after Mandatory Projects

c. Rest of the projects should be Ranked after ranking Mandatory and Government projects

 

Ranking should happen based on Projects but it has to be done for a specific category of projects first and then move to the next category of projects

 

nathan_rudman

Natively, the function of Anaplan doesn't do that. However we can achieve it easily enough.

 

you can rank by type, which is what I did in "Rank within Groups" with this formula:

 

RANK(profit, DESCENDING, SEQUENTIAL, TRUE, type)

 

So logically what we need to do it to add to the ranking within the type the max value of the previous types.

 

You'll need a system module for the next steps, like this:

Max rank = getting the highest rank of each type =  'forum: rank by group'.Rank Within Groups[MAX: 'forum: rank by group'.type]

Priority = you get it

Value to add = cumulating the max value of the previous groups = RANKCUMULATE(Max Rank, Priority, ASCENDING, TRUE)

Previous Type = just set the previous type in terms of priority so that we can bring the previous type's value to add

 

 

Now if you go back to the original module, a few more line items:

previous type = we cannot do a double depth lookup so we need to bring the "previous type" at this level = 'forum: type ranking'.Previous Type[LOOKUP: type]

Final Ranking= 'forum: type ranking'.Value to Add[LOOKUP: previous type] + Rank Within Groups 

 

To be noted that the priority and previous type inputs can be fully automated by just using the order of the items in the list and FINDITEM if there was a longer list. Didn't go into it not to confuse.

 

kavinkumar

HI @gaurav_sharma-bng,

Interesting post!!

Check this out. Please create the below highlighted line items in your module and make sure to change the 'Applies to' as well. Then replicate the same formula as mentioned in the SS.

 

 Before applying sort,

 I hope this the what the outcome should be.

 

I am not a big fan of using select. Please avoid using Select function by having ' SYS Lookup Module'.

I hope this helps! Let me know if you stuck somewhere.

Thanks and Regards,

Kavin.

gaurav_sharma-bng

Thanks a lot Kavinkumar, it is a valid solution. I will implement this

gaurav_sharma-bng

Thanks a lot Nathan, it is a great solution