Formula with Variable Stored in Variable

[#1]

I have a bunch of calculations/formulas stored as variables in an application.  I am trying to move the definition of these variables to a QVS file for easier maintenance.  However I am running into problems when the formula includes a reference to a variable, these variables are getting evaluated and 'removed' from the formula because the variable as not value at the time the script is run as it is set interactively in the application.

Example:

1. Variable definition as defined directly in the application, two variables are referenced vPer1Min & vPer1Max
   • Sum({$<[c_Invoice Type]={'NORMAL'},%dtl_date_key={">=$(=vPer1Min)<=$(=vPer1Max)"}>} [Item Net Sales (USD)]
2. Variable definition in QVS file
   • Method 1 - Using Set:
     • Script Line: Set fInvPer1SumUSD = 'Sum({$<[c_Invoice Type]={''NORMAL''},%dtl_date_key={">=$(=vPer1Min)<=$(=vPer1Max)"}>} [Item Net Sales (USD)])';
     • Definition after script run: Sum({$<[c_Invoice Type]={'NORMAL'},%dtl_date_key={">=<="}>} [Item Net Sales (USD)])
   • Method 2 - Using Let:
     • Script Line: Let fInvPer1SumUSD = 'Sum({$<[c_Invoice Type]={''NORMAL''},%dtl_date_key={">=$(=vPer1Min)<=$(=vPer1Max)"}>} [Item Net Sales (USD)])';
     • Definition after script run: Sum({$<[c_Invoice Type]={'NORMAL'},%dtl_date_key={">=<="}>} [Item Net Sales (USD)])

What do I need to add to the script line so that the variable vPer1Min and vPer1Max are not evaluated when the script is run?

[rubenmarin · Accepted solution]

HI Jessica, when script founds $( it tries to evaluate, one way to avoid this is using another character and Replace():

Let fInvPer1SumUSD = Replace('Sum({$<[c_Invoice Type]={''NORMAL''},%dtl_date_key={">=#(=vPer1Min)<=#(=vPer1Max)"}>} [Item Net Sales (USD)])', '#', '$');