Calculation based on variable selection from a user

MatthewWilcox

Hi team,

Got a problem I am trying to work through and have hit a roadblock.

I am working on a forecasting tool and having trouble with dynamic selection of variables for basing my calculations on.

For the purposes of this I have a list of accounts which is 62 members long. I have a driver module it is dimensioned by time, accounts and scenario. There are up to 8 drivers that my line items where the user inputs the driver value for each scenario (low, med, high stressed).

I have a calculation module that loses the accounts as a dimension as each account uses the drivers in a different manner so each account is now my line item. I finally have a output module that uses the line item subset from my calc module to consolidate it all back. This all works fine as intended.

The problem I have is, I would like to allow dynamic switching of the drivers entered from the different scenarios. In my head I have a- mapping module (SYS01) dimensioned by accounts and a line item which is the list of scenarios . I then have a second calculation module where the line items are the same as the previous calculation module except the formula would be based on the selection of the scenario from the mapping module. I dont think the formula works so I need a workaround.

Here is my calculation module

my mapping module

How I imagine the formula you can see the issue is that there is a select within a select. how would I correct this?

any suggestions would be appreciated.

An alternative considered would be for my calculation module to maintain the accounts dimension but then have massive nested if's for the calculations which is less than ideal and even then I do not know if that would work

MatthewWilcox

Think I solved it.

Have a second driver module that pulls the scenario selected in the mapping module that feeds into the second calculation module.