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!
Best Answer
-
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
0
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!
0 -
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
1