Dynamic Variable in QVS/Load Script

[#1]

Hi

I am trying to calculate a bunch of variables that dynamically calculate some KPIs based on a slider. The slider changes the variable "vActualMonth".

When I define the variable manually, it works just perfectly fine. When I try to define that variable in the load script or in a QVS, the variable within the variable is replaced with the value and it no longer works dynamically.

Here is what I would need:

SET vKPI_AATotalCost = '=Sum({$<RMonth={"<=$(vActualMonth)"}, RType={"Actual"}, AcctCode=>} If(AcctCode >= 6000 AND AcctCode <= 6999, InvAmount))';

When I define this in the load script, the variable is defined as:

=Sum({$<RMonth={"<=6"}, RType={"Actual"}, AcctCode=>} If(AcctCode >= 6000 AND AcctCode <= 6999, InvAmount))

So the $(vActualMonth) is replaced with its value 6. How can I prevent this to happen?

I know I could define the variables manually, but because it's quite a bunch of them, it would be much more efficient, to have that done in the script...

Thanks for any input.

[tresesco]

One way is to use replace() to use '$' indirectly. Check: Re: Variable interpreted in script

[jagan · Accepted solution]

Hi,

Try like this

LET vActualMonth= 5;

LET vKPI_AATotalCost = '=Sum({$<RMonth={"<=$' & '(vActualMonth)"}, RType={"Actual"}, AcctCode=>} If(AcctCode >= 6000 AND AcctCode <= 6999, InvAmount))';

Hope this helps you.

Regards,

Jagan.

[jagan]

Better expression using only set analysis

LET vActualMonth= 5;

LET vKPI_AATotalCost = '=Sum({$<RMonth={"<=$' & '(vActualMonth)"}, RType={"Actual"}, AcctCode={">=$(6000)<=$(6999)"}>}InvAmount)';

Regards,

Jagan.

[#5]

Excellent. That was what I was looking for. Thanks a million...