Return list member with highest value in module on other dimension

Options
QZ
QZ
in Integrations and extensibility

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 !

Answers

  • Konstantin97
    Konstantin97
    Options

    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:

    image.png

    Module A blueprint:

    image.png

    Module A Standard view:

    image.png

    Module B Blueprint:

    image.png

    Module B Standard view:

    image.png

    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

  • QZ
    QZ
    edited October 2023
    Options

    Hello Konstantin,

    Thanks a lot for your input. Indeed that seems to save some space over what I currently have. Although what I did not mention is that Module B would contain other data than the Market ranking and such data should not have the rank dimension then.

    I can try your solution with a Module C that would have only Product as dimension to see how this goes in my dashboard or maybe there is a way to still get it in Module B while saving space.

    Thanks !

Categories