Rank list items based on multiple ranking levels

LouiseBourgonjon
LouiseBourgonjon
in Modeling

Hi Anaplan users,

 

I am trying to rank PO values by cost center & cost element & period & vendor & PO value.

See screenshot and blueprint view attached to follow my reasoning below:

 

I first want to show all POs from the vendor with the highest PO value at vendor & cost center & cost element & period level. That is what I calculated in the line item "Rank by vendor total". Lowest rank is the vendor with the highest PO value.

Then, within this vendor, I want to rank the POs descending according to PO value in rep currency. I calculated the line item "Rank within vendor" which I need to use in ascending order. (1 is the PO with highest value for that vendor).

Somehow I will need to come to a combination of the two line items to calculate one rank. 

In order to do that, I would like to the rank by vendor total to go from 1, to 2 to 3,... instead of jumping from 1 to 10 to 24,... as you can see in the screenshot attached. 

How can I change my rank by vendor total formula so that it jumps with steps of 1? 

If I have accomplished that, I can multiply the two rankings and I can use that as the final ranking. That should work.

 

Thank you for hinting me towards the correct formula!

Tagged:

Best Answers

  • TimothyThomas
    TimothyThomas
    Answer ✓

    Hi @LouiseBourgonjon ,

     

    I thought I posted this yesterday, but it appears maybe my post didn't take. Regardless, let me know if this solution potentially works for you:

     

    1. Create new line item "First Occurrence?" which checks for the first occurrence of the specific vendor you are ranking within the module.
      • Formula: IF NOT ISFIRSTOCCURRENCE(Vendor Text, Transactions_flat) THEN FALSE ELSE TRUE
    2. Create new line item "Rank by vendor total staging": Only ranks the vendors if it is the first occurrence of that specific vendor, and uses the first occurrence line item as the include value argument for the Rank function.
      • Formula: IF NOT First Occurrence? THEN 0 ELSE RANK(Value in rep currency by vendor, DESCENDING, MINIMUM, First Occurrence?)
    3. In separate module dimensionalized only by Vendor, create rank formula referencing the staging module.
      • Formula: 'OUT3_Drill-down to transactional level'.Rank by vendor total staging[SUM: 'OUT3_Drill-down to transactional level'.Vendor List]
    4. Adjust formula in the end result rank to reference this rank, looking up against the vendor list line item.
      • Formula: 'Calc: Vendor'.Rank[LOOKUP: Vendor List]

     

    Let me know if that works for you. Of course, the additional line items will add some space, but hopefully not too much to make this less feasible. I'm sure there are other ways to make this work as well.

     

    Thanks,

    Timmy

  • LouiseBourgonjon
    LouiseBourgonjon
    Answer ✓

    Hi @TimothyThomas and @whitby ,

     

    Thank you very much for the suggested solutions.

    The solution of Timothy seemed to be the most sustainable to me so I tried it out. It took me some more time as the vendor should be ranked within transactions belonging to the same cost center, cost element and period (instead of ranking it to the full transaction list). I had to create a line item which holds the combination of CC_CE_Period and reference that in the ISFIRSTOCCURENCE and also needed to add that as a dimension in the module holding the rank by vendor (and thus also by CC_CE_Period combination). But in the end, it worked out! (see attached)

    Thank you for the help!

Answers

  • whitby
    whitby

    Hi @LouiseBourgonjon 

     

    Would this solve your problem?

     

    Create new (third) line item that takes:

    (100 x <line item 1>)+ <Line item 2>

    i.e.

    (100 x 'Rank by Vendor Total' )+ 'Rank within vendor'

     

    This way all your items should be sorted correctly (note - if you have more than 100 members will need to multiply by 1000 for this to work:

    whitby_0-1647357425048.png

     

     

  • LouiseBourgonjon
    LouiseBourgonjon

    Hi @whitby ,

     

    Thank you for coming back to me. 

    I think your solution would work, however, I have 80.000 items and the list keeps growing every month, so I am looking for a more "sustainable" solution which does not depend on the number of list items.

    I think maybe the way my rank formula in the line item is structured can be changed to avoid jumps from 1-10-24,... and go in jumps of 1. That would really solve this issue.

  • TimothyThomas
    TimothyThomas

    Oops, under Step 3 I meant "referencing the staging line item." not module.

  • Soumyajaiswal
    Soumyajaiswal

    How can we get Rank within Vendor in Anaplan?

Categories