Return list member with highest value in module on other dimension

QZ

Hello,

I have two modules:

"Module A" with two dimensions: "Product" and "Market"

"Module B" with one dimension: "Product"

Module A has a line item "Sales" that has the sales by Product and Market.

I would like to display the top 3 Markets (formatted with the "Market" list) with the highest sales in Module B, with three line items: "Highest Sales", "2nd Highest Sales", "3rd Highest Sales".

I managed to get the information but not without creating a large number of empty cells which will clutter my model at some point. In Module A I used RANK to display the market's own name if it is the highest selling for this product (or 2nd, 3rd, in separate line items) in its own Product x Market combination:

In short if Rank = 1 then item(Market) else blank (same for Rank = 2 etc) in Module A then creating the same three line items: "Highest Sales", "2nd Highest Sales", "3rd Highest Sales" in Module B with a LOOKUP for Product. So if I have 20 markets, I have 19 out of 20 celles that are empty in this module for each ranking line item: 1st, 2nd and 3rd highest.

Is there another way to get that done without having to keep the formula and the clutter in Module A ?

Thank you very much !

Konstantin97

Hi @QZ!

Such problems usually isn't easy to solve in Anaplan and often it takes a lot of space and performance.

But I have a few ideas which can help you.

1. You can create a list of ranks: {"Highest", "2nd Highest", "3rd Highest"}
2. You can map market to items of this list for each product

Here are some screenshots of my build:

List of Ranks:

Module A blueprint:

Module A Standard view:

Module B Blueprint:

Module B Standard view:

After this you can select any item from the Rank items list to use or leave module B with Rank Items in the dimensions.

Once again this doesn't seem like a best solution, but at least it could save some space and improve performance.

Also consider using multiple IFs instead of finditem as on my screenshot.

Hope it helps!

Thanks,
Konstantin