Need help with SUM and/or LOOKUP functions

YS_*12825

I'll be using 'Formulas and functions' app as a base for this.

We have 'Transaction Values' module:

screenshot.44.png

I created 'adv prod' list and 'T: map' module. It's goal is to assign basic products to advanced products:

screenshot.45.png

Any product can be assigned to no advanced product, to one or many.

My goal is to write a formula for this simple module:

screenshot.46.png

Expected result is:

screenshot.44-1.png

I would like to avoid adding ANYTHING to Transactions module - let's assume that it is so huge that every new line item impacts performance. Other than that - I can add line items or properties where needed.

I have run out of ideas for this seemingly very simple calculation. Any help appreciated. Thanks!

Accepted answers

DavidSmith

@YS

It's actually very simple

In your mapping module, add a new line item

If the mapping is ticked, then sum on the product field in the transaction module

you can then just point to that line item in the target module; the top level item summary will bring the values through

David

All comments

DavidSmith

@YS

It's actually very simple

In your mapping module, add a new line item

If the mapping is ticked, then sum on the product field in the transaction module

you can then just point to that line item in the target module; the top level item summary will bring the values through

David

YS_*12825

Very simple and elegant indeed! Thanks!

I expected the solution to be for result module, but putting it in mapping module makes it so much easier!

DavidSmith

@YS

It often is. Anaplan's dimensional strucutres mean we shouldn't need to build complex formulaes with multiple levels of IFs, SELECTS, etc..

The approach to take is as follows:

Look at the dimensionality of the source and the target. You need to establish a link between the two and often you need a staging or intermediate module to do that

In this case you had Transactions as the source and Advanced Products as the target; there is no direct link between the two

The mapping module with Advanced Products and Products provides that link, in this case into the mapping module itself

So we went

1. Transactions (with Products as a formatted line item)

2. Products and Advanced Products (linked via the Products line item in Transactions)

3. Advanced Products

So 1-2 is linked via Products and 2-3 is linked via Advanced Products and the Top Level of Products

Cheers

David

Quick Links

Can you help?

Unanswered questions

Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385