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:

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!

Tagged:

Best Answer

  • @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 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

     

    David 

     

     

Answers

  • 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!

  • @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