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!

DavidSmith

Great points, and I absolutely agree. The transformation between modules is key to good model design and it is what makes Anaplan so special; the ability to only hold the relevant dimensionality when required.

Visualisation for multi dimensional modules is tricky, so I, to always try and visualise the relevant dimensions when 

 

I would also recommend using a System module (from D.I.S.C.O.) to hope the mappings and visualise this in the middle (as below)

You can even undock (or float) the modules to help with the visualisation

 

And if you are stuggling to understand if it should be a SUM or a LOOKUP, use the following phrase when comparing the lists of the source and system module dimensions.

If the Source is the Same, it's a SUM!! 

 

One final point on SELECT.  I would also recommend using a formatted line item rather than a hard coded SELECT for detailed items.  This gives you the flexibility to change the SELECT should the requirements change without needing to find all of the occurences within line item formulae.  The exceptions are Versions and Top Level items but for all other SELECTs, use a formatted line item in a central "global" assumptions module

 

David

LipChean_Soh

Hi David,

 

What do you mean by 'formatted line item' in the statement below:

 

One final point on SELECT.  I would also recommend using a formatted line item rather than a hard coded SELECT for detailed items. 

 

Thanks

DavidSmith

@LipChean_Soh

Sorry, maybe should have been clearer - You format a line item to the same list that the SELECT function is referencing.  You then use a LOOKUP function referencing the new formatted line item

David

Alessio_Pagliano

Hi,

If I remember correctly this is also a pre-requirement when using ALM?
I had a few SELECT statements and I was almost forced to create a central module, using lookups instead.

Thanks,
A

DavidSmith

@Alessio_Pagliano

It's good practice to try and avoid SELECT anyway because it reduces flexibility and LOOKUPs often offer that flexibility and optimised calculations

And, unintentionally, the ALM restriction helps promote best practice, so all good in my book!!

David 

Alessio_Pagliano

Thanks @DavidSmith

We have a small Peer review guide for our models and avoidance of SELECT wherever possible is one of the points that can be find in there 🙂

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

akhil.kohli

Hi Cedric,

 

Ideally, Lookup should work in this case. You can have a parent format for your lookup line item and then in the destination line item you can point to this lookup line item.

 

e.g. You have Country>Region as a hierarchy and you want to see the values for London. In this case, the lookup line item which has nothing in applies to will have a format of country and the value will be London.

 

In the resulting line item, you can use the above line item as a lookup.

 

Let me know if this was not as expected or I misread your question

 

Regards,

Akhil