4 Replies Latest reply: May 7, 2013 5:37 AM by zaintmc123

# 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.

• ###### Re: Variable Issue in Excel

Hi Zain,

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

Hope that will work for you.

Jonathan

• ###### Re: Variable Issue in Excel

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

'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

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

• ###### Re: Variable Issue in Excel

Perfect, thanks a lot Gysbert.

Regards,

Zain.