Formula with Variable Stored in Variable

Thu, 15 Jun 2017 18:11:56 GMT

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:

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

Re: Formula with Variable Stored in Variable

rubenmarin — Thu, 15 Jun 2017 18:22:42 GMT

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)])', '#', '$');

topic Formula with Variable Stored in Variable in QlikView

Formula with Variable Stored in Variable

Re: Formula with Variable Stored in Variable