YS Occasional Contributor
Occasional Contributor

Need help with SUM and/or LOOKUP functions

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


We have 'Transaction Values' module:


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



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


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



Expected result is:


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!

Community Boss

Re: Need help with SUM and/or LOOKUP functions


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 module2018-10-09_13-36-59.png

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





YS Occasional Contributor
Occasional Contributor

Re: Need help with SUM and/or LOOKUP functions

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!

Community Boss

Re: Need help with SUM and/or LOOKUP functions


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