How to pull together values from multiple modules

ChrisMcCarthy

Looking to pull together values from multiple modules, which have started, as shown in the right hand screenshot. The difficulty I have is trying to pull the values across from a module such as INP08, shown on left hand side of screenshot below. Ideally I want to be able to pick up individual lines from this module into new line items in the Interest summary module at right hand side. Any suggestions would be welcome

JFAnaplanner

There isn't really a downside in terms of calculation times. A LOOKUP is the same as a SELECT except lookup is a dynamic and based on cell values in a line item whereas a select is hardcoded to what it is looking up in the formula. There are certain use cases where you need to use a SELECT (such as grabbing a specific native version for variance analysis or getting something static that won't change in the future like "time.all periods").

The big no no is combining a LOOKUP and a SUM in the same formula - this will have a performance impact when you start doing it on bigger modules.

Glad you got it working!

JFAnaplanner

Hi Chris,

It is hard to know the problem that you are facing without seeing the difference's in dimensionality that you are seeing between the source modules and the target module. Could you share screenshots with the dimensionality of the two modules and highlight the line items that you are trying to connect (in blueprint view)?

Best,

Jacob

ChrisMcCarthy

Hi Jacob,

Firstly, many thanks for your initial response. Hopefully the following screenshots will help, the first of which is from the source module

I would want to be pulling through the value from this module from the "Value GBP Latest" line item. It would be for certain "ID", so for example, in the target module shown below I have a line item "ROU Interest", this would be #385 as depicted below - it would be a different ID for eacg of line items shown in Interest Summary module below.

Let me know if anything else would be of use

JFAnaplanner

Hi Chris,

Ok so I can see that you are going from 3 dimensions (Forecast Version, Template line item Descriptions & time Months) to at least 2 dimensions (Forecast Versions, Currency - Latest). The reason I say 2 being that you might have time on the interest summary module but I can't quite see. I'm assuming you aren't using native versions on either module since you have a versions list.

The Mapping for the Forecast versions is fine since this is the same between modules, if you have time turned off in the target then Anaplan will reference the summary lines of the target (since you have these turned on) and if you have time on then it should map nicely.

I think the problem that you are having is the change between the Template line item Descriptions and the Currency - latest dimensions. If there is a one to one relationship between these then you need to make sure that you have a line item in a system module referencing this and then you can do a LOOKUP to map them. Otherwise if there is a many to one relationship then you would want to go with some sort of aggregation function - most likely SUM to tell anaplan how to group the source items into the list on the target line items.

Best wishes,

Jacob

ChrisMcCarthy

Time is on yes. Correct, we are not using native versions. I've attempted doing sum, however receive the following error

JFAnaplanner

You are trying to put the mapping for just 1 list item into the SUM function, the SUM function needs a line item to work not a list item. you want to only do one list item then use the SELECT function, the only problem with this is it is hard coded into the formula so it isn't dynamic.

SELECT's/LOOKUP's are for 1:1, SUM are for Many:1

Best practise now is to make a constants module, format a line item as a list, set the line item to the item you want to lookup and then do a lookup to the constants module rather than using a hardcoded SELECT formula. If the mapping ever needs to change in the future you can update the line item easily and even expose it to the end users on the UX if needed.

Try to get it working with a SELECT for now, worry about the bp once you get a formula working!

ChrisMcCarthy

I've created a "constants" module as you suggest , without time on

Thought I had it clear what I needed to then do, however when try and complete formula I get the following

ChrisMcCarthy

I seem to have got this working with a LOOKUP, rather than SELECT. Does this have a downside in terms of calculation times?