topic Re: Variable Issue in Excel in QlikView

Variable Issue in Excel

Tue, 07 May 2013 09:02:53 GMT

Hi,

i made variables and its expression in excel sheet and i have an issue is when i define variable in expression its show me blank

like:

vKPI.RTVRatio.RTVPer (variable name)

if(getselectedcount(Year)=0,

(Sum({$ <[Fact Type]={"Transaction"}, TransType = {"CR"}, Revenue_Flag_Value={"1","-1"},Year={$(vThisYear)} >} Price)),

(Sum({$ <[Fact Type]={"Transaction"}, TransType = {"CR"}, Revenue_Flag_Value={"1","-1"} >} Price))

)

its show me

if(getselectedcount(Year)=0,

(Sum({$ <[Fact Type]={"Transaction"}, TransType = {"CR"}, Revenue_Flag_Value={"1","-1"},Year={ } >} Price)),

(Sum({$ <[Fact Type]={"Transaction"}, TransType = {"CR"}, Revenue_Flag_Value={"1","-1"} >} Price))

)

Kindly guide me the solution.

Regards,

Zain.

Re: Variable Issue in Excel

jonathan_dau — Tue, 07 May 2013 09:08:52 GMT

Hi Zain,

I'll try something like Year={'$(vThisYear)'} or Year={'$(=vThisYear)'}

Hope that will work for you.

Jonathan

Re: Variable Issue in Excel

Gysbert_Wassenaar — Tue, 07 May 2013 09:17:07 GMT

Try using the replace function:

//loop over rows in Formulas worksheet

FOR i = 0 to '$(RowCount)'

//create temporary variables

let TempVarName = peek('VariableName',$(i),'Formulas_Temp');

let TempVarValue = replace(peek('VariableValue',$(i),'Formulas_Temp'),'$','╚');

//create and assign the "permanent" varaiable with its value from the Formulas worksheet

let $(TempVarName) = replace('$(TempVarValue)','╚','$');

Re: Variable Issue in Excel

jonathan_dau — Tue, 07 May 2013 09:22:43 GMT

Or try to change your variable load with LET

Let RowCount = NumMax(NoOfRows('Formulas_Temp'),0)-1;

FOR i = 0 to '$(RowCount)'
       let TempVarName    = peek('VariableName',$(i),'Formulas_Temp');
       let TempVarValue   = peek('VariableValue',$(i),'Formulas_Temp');

      let $(TempVarName) = '$(TempVarValue)';
NEXT

and change your formula in your file :

'if(getselectedcount(Year)=0,

(Sum({'&chr(36)&' <[Fact Type]={'&chr(34)&'Transaction'&chr(34)'}, TransType = {'&chr(34)&'CR'&chr(34)&'}, Revenue_Flag_Value={'&chr(34)&'1'&chr(34)&','&chr(34)&'-1'&chr(34)&'},Year={'&chr(39&chr(36)&'(=vThisYear)&chr(39)&'} >} Price)),

(Sum({'&chr(36)&' <[Fact Type]={'&chr(34)&'Transaction'&chr(34)&'}, TransType = {'&chr(34)&'CR'&chr(34)&'}, Revenue_Flag_Value={'&chr(34)&'1'&chr(34)&','&chr(34)&'-1'&chr(34)&'} >} Price))

Re: Variable Issue in Excel

Tue, 07 May 2013 09:37:49 GMT

Perfect, thanks a lot Gysbert.

Regards,

Zain.