Importing formulae into variables - Can i stop qlikview trying to evaluate the formula when loading?

[#1]

Hi,

Can anyone help with the following

I have a table containing the ScorecardMonth and a string that I want to use as part of a formula in a scorecard

e.g.

ScorecardMonth YTDFormula

May 2010 {<Month = {'Apr 2010', 'May 2010'}

Jun 2010 {<Month = {'Apr 2010', 'May 2010', 'Jun 2010'}

I then have a variable in my document which is set to the value of YTDFormula

YTD = YTDFormula

So that when I change the scorecardmonth, the variable updates

Then, finally, I, have another set of variable, which utilise this YTD variable

For instance

vKPI1 = Count($(YTD), EmpStatus = {'Active}>} [Employee NO])

This works, the formala work and update correctly when i change the scorecard month.

Problem arises when i try and load the values for my KPI variables. I use the script as follows to load the variable values and names from an table which i hold in Excel

LOAD

YTDVariableName

,

YTDVariableValue

FROM

KPILoad.xls

(

biff, embedded labels, table is KPIs$)

;

LET

RowCount = NumMax(NoOfRows('VariableList'),0)-1

;

FOR

i=0 to

'$(RowCount)'

LET

TempVarName = peek('YTDVariableName',$(i),'VariableList')

;

LET

TempVarValue = peek('YTDVariableValue',$(i),'VariableList')

;

SET

$(TempVarName) = $(TempVarValue)

;

NEXT

This loads most of the variables perfectly, apart from when one of the variables contains reference to another variable

e.g.

vKPI1 = Count($(YTD), EmpStatus = {'Active}>} [Employee NO])

gets loaded as

vKPI1 = Count(= YTDFormula, EmpStatus = {'Active}>} [Employee NO])

because Qlikview has resolved the variable in the script, i think it does this at the stage where I

LET

TempVarValue = peek('YTDVariableValue',$(i),'VariableList')

Is there anyway i can stop Qlikview doing this and just get it to load the string for my variable? I've tried using SET instead of LET but then it sets the variable to

peek('YTDVariableValue',$(i),'VariableList')

Any ideas? Thanks in advance.

[#2]

I think this may have to do with th order that things are being processed in the script and wether you are letting or setting. 'let' tells qlikview to evaluate the expression where 'set' just stores as e.g

set x = 1+1; // x is stored as the string "1+1"

let y = 1+1; //y is stored as integer "2"

thats why changing

LET TempVarValue = peek('YTDVariableValue',$(i),'VariableList')

into

SET TempVarValue = peek('YTDVariableValue',$(i),'VariableList')

doesn't work as the Let is required to allow the evaluation of the peek and $(i).

Since i see no entry in the script along the lines "set YTD = YTDFormula;" then i assume you are delcaring and setting that within the document, i.e. usuing the on-open trigger. If that is the case then there is a viable workaround (although not a perfect solution).

prior to loading YTDVariableName and YTDVariableValue, create an entry that states:

set YTD = '$'&'(YTD)'

that way when YTD is evalauted by QV it merely concat's itself into the working expression (as QV only evaluates 1 level).

once the document opens, your "on-open" trigger then resets YTD to YTDFormula and everythign carrys on as normal (in theory).