Getting data by line item into a dimension and generalising a complex formula
I want to calculate some values in my "Forecast" using a formula that is almost the same for each value. The only difference is that each row in the "Forecast" module gets an initial seed value from a different line item in the "Base Data" module.
The formula I want to use is actually a lot more complicated than shown in the example here, and instead of 4 rows, I have dozens, so I would like to not have to replicate the formula many times.
I've been working on doing this by building my "Forecast" module with a single Line Item, and moving the individual rows into a list. However, I can't work out how to write the formula in such a way that each list member in the "Forecast" module picks up the right value from "Base Data".
What I've got so far is:
- Build a Line Item Subset from the Line Items in "Base Data"
- Move the rows of the "Forecast" module to their own dimension
- Add a List-type Line Item to the "Forecast" module so that I can associate each row in that module with its appropriate source row in "Base Data"
- Create a single Line Item in the "Forecast" module whose job will be to to pull the appropriate value from the "Base Data" module and calculate the required results
But I can't see how to write the formula for the Line Item in step 3 so that it pulls the right value from "Base Data". If everything in "Base Data" was just data, it would be easy because I could set up "Base Data" with a single Line Item and move its rows into a list, but some of the rows in "Base Data" are formula-driven and therefore (I think) the rows in "Base Data" have to be Line Items.
Any ideas gratefully received.