Referencing List Items in Formula

davargas
davargas
in Integrations and extensibility

I have module D03 that shows how many slots each product family uses. D03 is dimensioned by Time on rows, and PR2 Product Family list and 1 line item 'Slots' on columns. I am trying to write a formula that 1. references data from another module, and 2. does some calculations for all the data in each item for PR2 Product Family. 

IF
    ITEM('PR3 Product Name') = 'SYS06 Capacity Pointer'.Capacity Pointer
THEN
    'D02 Planned Capacity'.Corrected Capacity / 6
ELSE
    'D01 Slot Forecast'.Slots
 
The IF and THEN part are for 1. It references data from another module and inserts it into a specific item in PR2 
The ELSE part just returns the normal data for the module.
 
In this case, objective 2. does not apply to that specific line item in PR2, so it works perfectly to just enter the calculation in the ELSE part. I am trying to do something like this.
IF
    ITEM('PR3 Product Name') = 'SYS06 Capacity Pointer'.Capacity Pointer
THEN
    'D02 Planned Capacity'.Corrected Capacity / 6
ELSE
    'D01 Slot Forecast'.Slots * ('D01 Slot Forecast'.Slots('PR1 Product Lines'.All Products - 'PR2 Product Families'.other))
 
This doesn't work obviously because in the ELSE part, it is just referencing the list items rather than referencing the data corresponding to the list items in this module. I want the rest of PR2's 'Slots' to calculate
(Slots * (Slots/(Total Slots-Other Slots))
'other' is a list item in PR2 and 'Total Slots' is just the total of all slots in the module (also a list item).
How can I reference specific data from the module when writing a formula? I want to make calculations using members of the list. In excel I can just click on a cell and write a specific formula, what would the equivalent of that be on Anaplan? 
Tagged:

Answers

  • luke_e
    luke_e

    Out of curiosity, how many products/families do you expect to calculate for and with how many different calculation iterations?

     

    There is a way to do what you're looking at, but it typically involves a significant number of nested IFs and if you add a new product, you're back to updating the calculation (I tried to do something similar for a P&L with accounts that had different methodologies.. it was a mess). I also imagine it's going to hit the calc engine hard as well given all the loops.

     

    Without knowing the scope of work, you may be better off calculating in separate modules (using line items + subsets) and then consolidating with your broader list in an output module.

Categories