Ranking Project - One Category at a time

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

Capt1111ure.PNG

 

Best Answers

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

    ranking by type.png

     

    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:

    ranking by type settings.png

    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.

     

  • 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.

    image.png

     

    image.png

     Before applying sort,

    image.png

     I hope this the what the outcome should be.

    image.png

     

    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.

Answers