Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Variable Issue in Excel

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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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)','╚','$');

NEXT


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
jonathan_dau
Contributor III
Contributor III

Hi Zain,


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

Hope that will work for you.

Jonathan

Gysbert_Wassenaar

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)','╚','$');

NEXT


talk is cheap, supply exceeds demand
jonathan_dau
Contributor III
Contributor III

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))

)'

Not applicable
Author

Perfect, thanks a lot Gysbert.

Regards,

Zain.