Modelling of P&L table with different variables per line

[DIH]

I need to modify an existing P&L table with several scenarios (using the Vizlib solution with external Excel table).

Is it possible in a P&L table to use different factors per line?

sum(  Value* $(=PL_View_Factor_Best) )

I get the right calculation, when the factor is the same for all lines, but when using different values or even a conditional value for different scenarios, it returns no value.

Is there another/better way to solve this?

[marcus_sommer]

Just using: sum(Value * Factor) should be working. This means you need an unique Factor against the dimensional layer.

[DIH]

Hi Marcus,

well, that´s my issue.
All lines in the P&L statement can have different factors to multiply with. 

Do you have also a solution for this case?

[marcus_sommer]

If it's not unique against the dimensions of the view you need to apply it to a more granular layer maybe even on the atomic level.

[DIH]

I have several multipliers, that should be based on variables, that will be altered on the sheet:

v_Best = 1.2;
v_Worst = 0.8;
v_Normal= 1.05;

T_Factor:

ID   Best,
10,  1.1                                     -> fixed values works fine
20,  $(v_Best)                          -> single variable works fine
30, 1.1*1.2                               -> combination of fixed values does not work
40, (1-$(v_Worst))                   -> calculation of values does not work
50, 1.5*$(v_Best)*$(v_Best)   -> calculation of several values does not work

I'd like to calculate, based on the ID and a corresponding table with values:

SUM(Value*Best)

[marcus_sommer]

This should work:

let v_Best = 1.2;
let v_Worst = 0.8;
let v_Normal= 1.05;

T_Factor:
load ID, evaluate(Best) as Best inline [
ID, Best,
10, 1.1
20, $(v_Best)
30, 1.1*1.2
40, (1-$(v_Worst))
50, 1.5*$(v_Best)*$(v_Best)
];

[DIH]

As a static solution it could be used.

...but the task for me is to make these variables flexible, so the user can change them in the sheet, getting the recalculation right away.

[marcus_sommer]

That's not possible - at least not with variables. Because a variable is always independent with no direct association against other variables or field-values.

If any context-depending results are intended you will need to query the context and then branching appropriate - which means the use of approaches like:

if(context ='x', var1, if(context = 'y', var2, ...))

or more readable per:

pick(match(context, 'x', 'y'), var1, var2)

which also means that you couldn't use a simple: sum(Field * var) else the var-part must be a bit more complex.

For rather simple what-if-scenarios is this a pragmatic approach and the few context-loops within the calculation won't need too much efforts and probably also not seriously impact the performance.

For more advanced simulation games I wouldn't use variables anymore - I did it already with hundreds of array-variables containing thousands of parameter - because the handling of the variables and addressing the context is too heavy and complex and any kind of maintaining is a nightmare.

IMO much simpler and my current approach is to use field-values and selections provided per island-tables, here a very simple example:

t1: load floor(1 + (-0.3 + (recno()/10)), 0.1) as X autogenerate 6;

and then just a value of X is directly selected respectively a possible values and used as factor.

The only disadvantage of such an approach is that no "individual" values could be chosen else only available values but like the above example shows they need not mandatory to specified manually else be generic created and in more advanced versions with an external control of the from-to and the steps as well as with multiple layer it becomes very powerful.

[DIH]

Thank you very much for your help. I will rethink the approach.