Variable Issue in Excel

[#1]

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.

[jonathan_dau]

Hi Zain,

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

Hope that will work for you.

Jonathan

[Gysbert_Wassenaar · Accepted solution]

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

[jonathan_dau]

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

)'

[#5]

Perfect, thanks a lot Gysbert.

Regards,

Zain.