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

Sorting in Combined Grids
Hi Team, I have been trying to apply a sort based on a column in one of the combined grids and seems like the option is disabled. Does anyone has any idea on this? Regards, Aakash Sachdeva
Workaround for Formula & Closing Balance summary methods together in Polaris
Hello folks, Does anyone know how to work around the Polaris limitation of using the Formula or Ratio summary method alongside the Closing Balance within the same line item? I have a Business Unit list where the total (All BUs) must display the Corporate (1 of 20 leaf items under All BUs total) number of Unique Customers.…
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…