Refer to variable with dynamic name

[njimack]

Hi all,

In the below pivot table, I'm currently using this expression:

Sum({<CCY={"USD"}>}Balance_USD)*v_Rate_Change_USD

I'm trying to enhance this by multiplying by a variable that will be updated via a slider.  But I'll have a different slider for each combination of country, GBL and currency.  So I want the first amount multiplied by the variable v_Belgium_CMB_USD and the next multiplied by v_Bermuda_CMB_USD, etc.  It feels like this should be possible but I've searched for ages and can't find anything that works.

Any help gratefully received!

[chrismarlow]

Hi,

I tried to do variable expansions, but think you hit a wall with that as they are evaluated once, so think you are limited to pick/match pattern to line up the rates. This will be cumbersome if you have lots of country/ccy/gbl combos (although you can build forumlae in Excel & copy paste).

Attaching a toy app anyway, which might be of use, if only to see how nasty it looks.

Cheers,

Chris.

[njimack]

Thanks.  I see what you mean about becoming cumbersome.  I'm going to have around 120 combinations of country/GBL/currency so I guess it's back to the drawing board!