Create a formula from text

Options
spencerm396
spencerm396 Member, ALL USERS Posts: 3 Occasional Contributor
in Planning & Modeling

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?

Tagged:

Comments

  • spencerm396
    spencerm396 Member, ALL USERS Posts: 3 Occasional Contributor

    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.

  • spencerm396
    spencerm396 Member, ALL USERS Posts: 3 Occasional Contributor

    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!

Categories