Converting data between line items with different lists

alec_judd

An important factor of Anaplan is transforming data between dimensions of modules and line items.  For example, if I wanted to convert my sales amounts, which are currently stored by employee and product, to a summarized level of sales region and product, I’d need to do a conversion to summarize the data assuming sales regions are not a parent of the employees.

One trick that I want to share with everyone that works for me is to use deduction of the lists that are common across the source and target modules.  What I mean by that is that you can write down (or picture in your head!) each of the lists that apply to the line item where the data is currently stored (sales by employee) and do the same for the target line item where you want to put the data (sales by sales region). From there, you can compare the two sets of lists to understand which lists apply in both line items.  This will help you easily decide which lists you can completely ignore/cross off and which you need to address specially.

It's a simple process, but after five years of building in Anaplan, this is still what I picture in my head as I’m working towards a completed formula, no matter the complexity.

An example for the visual folks in the crowd:

Here is the source module which has the revenue data that I’m going to pull from.

Here is the target module where I want to summarize the revenue data into.

So you can go through a simple deduction exercise to understand how the data needs to be transformed.

In this case, the lists Products, Versions, and Time apply to both line items.  The only two lists that don’t apply to both lists are Employees and Sales Regions.  Therefore, I’m going to need a conversion in my formula using either a Select, Lookup, or Sum statement to go from Employees to Sales Regions.

When writing the formulas, you’ll need to understand and select the right type of statement.  Below is my version of the guide to choosing.

A select statement is pulling the same value from one source item to all target items.  Example: Every sales region should have the same revenue which comes from employee Paul only. 

The lookup statement would be used when there is a 1:1 relationship between the source and target lists.  Example: Each sales region only has one employee who contributes revenue to it (East from Paul, West from Chris, North from Pete, etc.).  The lookup would refer to a property or line item which applies to the target list (sales regions) but is formatted as the source list (employees).

The sum statement would be used when there is a many to one relationship from source to target.  Example: There are 40 employees whose revenue should contribute to East, 50 employees whose revenue contributes to West, etc.  The sum would refer to a property or line item which applies to the source list (employees) but is formatted as the target list (sales regions).

Lastly, you just need to write the formula.  In this case, it would be:

Direct Revenue.Revenue[SUM: Employees List.Sales Region Property]

Good luck, and let me know if you have a different thought process when writing Anaplan formulas to convert data between lists!

cedrichabis5

@DavidSmith 

It seems the LOOKUP will not work on aggregates in a List. Is there anyway to avoid the SELECT in that case?

Cedric