Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi Zain,
I'll try something like Year={'$(vThisYear)'} or Year={'$(=vThisYear)'}
Hope that will work for you.
Jonathan
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
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))
)'
Perfect, thanks a lot Gysbert.
Regards,
Zain.