Create a formula from text

spencerm396

I am trying to perform a lookup across multiple modules, but would like to avoid a gigantic case statement.I'll try to illustrate with this example. There is a list called details and a module called data. I'm trying to aggregate the data into a new module called summary.

 

Right now, my formula is a massive conditional statement like:

IF ITEM('details') = 'details'.first_detail THEN 'data'.field_name[LOOKUP condition] ELSE IF...

 

What I want to write instead is something like:

'data'.{lookup field name from item('details')}[LOOKUP condition]

 

In other words, dynamically tell Anaplan what field to lookup from data based on the associated item. Right now I can generate the formula using CONCATENATE, but it resolves to text and will not actually run as a formula. Is there a way to force the text into a formula or a different way to achieve what I want to do?

spencerm396

Thanks for your comments. In retrospect I've realized this is not a wise solution for the Anaplan platform. If the underlying field name were to change, this formula would not longer resolve to an existing line item and errors could occur. I still need to rewire my brain to think like Anaplan and not Excel!

PaulRitner

Hi,

 

There are many paths and considerations for your question (and limited details).

 

The above said, here is one way this can be done:

• Include a line item in each of the source modules that map the data points into the single summary module (list items).  If there are data points in a source module that should be ignored, then include some logic to set the mapping line item to blank (so that the data will be ignored)... 
• In the summary module (where you are bringing data from the various modules together), the formula would look something like:   source1.data[sum: mapping]+source2.data[sum: mapping]+source3.data[sum: mapping], and so on...

This type of mapping is efficient and scalable. 

 

In some cases, common mappings can be stored in a single (separate) module based on common list(s).

 

Where there are many many data sources, the exact nature of how they are both staged/managed in anaplan, as well as how they are aggregated, can vary widely and is highly dependent on both the present use-case, as well as how the model is expected to evolve/grow over time.

 

If you want to include some additional details, we can flesh out nuances.  For example, what is the nature of the various data sources?  

 

Paul

 

 

spencerm396

I may be having trouble phrasing my question. What I want is to choose the field name from another module dynamically.

 

Imagine the data module has two fields: f1, f2

 

I want to get those fields from my summary module. I could do something like:

IF condition THEN 'data'.f1 ELSE 'data'.f2

 

What I'm asking instead is if I could do something (like what I would do in Excel) where I might create a new field in summary called one_or_two that contains either 1 or 2 based on some other condition.Then in summary I could rewrite my above statement as:

'data'.f & {'summary'.one_or_two'}

 

This would transition the conditional statement to the values in the field one_or_two and figure it out as I go. I would be concatenating strings together to construct a formula that would run.

PaulRitner

Hi,

 

I can't think of a way to do that (yet), but have thought about the possibility a few times.

 

At present, the only thing you can do is map to the two line items as you are.  

 

I prefer to avoid complex IF-THEN logic in favor of a mapping of some sort.  The primary reason for this is because its so difficult to read/update complex IF-THEN logic (especially months or years after the model was initially placed into production).

 

Good luck!

 

Paul