Create a formula from text


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?

Best Answer

  • spencerm396
    Answer ✓

    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!


  • 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:[sum: mapping][sum: mapping][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?  





  • 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.

  • 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!