Rank list items based on multiple ranking levels

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!

ranking_by_vendor_134117.JPG

OUT3_Drill-down_to_transactional_level_blueprint_134117.xlsx

Accepted answers

TimothyThomas

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:

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
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?)
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]
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

OUT3_Regular_View_134209.png

Calc_Vendor_Blueprint_view_134209.xls

Calc__Vendor_134209.xls

OUT3_Blueprint_view_134209.xls

LouiseBourgonjon

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!

ranking_by_vendor_works_134236.JPG

All comments

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:

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

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:

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
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?)
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]
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]

Thanks,

Timmy

OUT3_Regular_View_134209.png

Calc_Vendor_Blueprint_view_134209.xls

Calc__Vendor_134209.xls

OUT3_Blueprint_view_134209.xls

TimothyThomas

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

LouiseBourgonjon

Hi @TimothyThomas and @whitby ,

Thank you very much for the suggested solutions.

Thank you for the help!

ranking_by_vendor_works_134236.JPG

Soumyajaiswal

How can we get Rank within Vendor in Anaplan?

Quick Links

Can you help?

Unanswered questions

Introducing Anaplan Python API SDK
Hey everyone, I wanted to share the Anaplan Python SDK with you. It's a Python Library that wraps all Anaplan APIs and makes it easier to interact with Anaplan programmatically. It is mostly designed with Data Integration Scenarios in mind, but it does support all APIs including ALM, SCIM, Audit, and Cloud Works. Please…
Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
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…