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

    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

               

               

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