topic Dynamic Variable in QVS/Load Script in QlikView

Dynamic Variable in QVS/Load Script

Tue, 28 Oct 2014 08:21:18 GMT

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.

Re: Dynamic Variable in QVS/Load Script

tresB — Tue, 28 Oct 2014 08:29:59 GMT

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

Re: Dynamic Variable in QVS/Load Script

jagan — Tue, 28 Oct 2014 08:32:24 GMT

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.

Re: Dynamic Variable in QVS/Load Script

jagan — Tue, 28 Oct 2014 08:35:56 GMT

Better expression using only set analysis

LET vActualMonth= 5;

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

Regards,

Jagan.

Re: Dynamic Variable in QVS/Load Script

Tue, 28 Oct 2014 10:05:06 GMT

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