Avoid $(-expansion in SetAnalysis Variable

[hugmarcel]

Hi

I'd like to set a formula in a script variable vBudgetAmount:

SET vBudgetAmount = SUM({<$(='['&CONCAT({1<$Field={'Service Request - *'}>} DISTINCT $Field,']=,[' )&']=')>} Budgets)

The formula calculates the sum of Budgets, ignoring all the selections in all table fields starting with 'Service Request - '

Goal is to create variable vBudgetAmount having content:
SUM({<$(='['&CONCAT({1<$Field={'Service Request - *'}>} DISTINCT $Field,']=,[' )&']=')>} Budgets)

But I always find that the $( is causing problems, thus I am getting:
SUM({<>} DISTINCT $Field,']=,[' )&']=')>} Budgets)

instead.

How I can get this working?

Thx - Marcel

[Peter_Cammaert]

Something like this?

LET vBudgetAmount = 'SUM({<$' & '(=' & chr(39) & '[' & chr(39) & '&CONCAT({1<$Field={"Service Request - *"}>} DISTINCT $Field,' & chr(39) & ']=,[' & chr(39) & ' )&' & chr(39) & ']=' & chr(39) & ')>} Budgets)';

I may have missed something, and it definitley looks horrible, but you get the idea...

Best,

Peter

[stigchel]

There seems nothing wrong with your expression, can you try to create the variable in the front and look at the result?

Otherwise there may be something wrong with $Field={'Service Request - *'}>} not matching any fields (do you need the space at the end?)

Tested your expression in the following qvw

[Peter_Cammaert]

Precisely, in the front-end this works out ok because this is a later stage.

The OP asked how to accomplish this in the script, where $-sign substitution will wreak havoc everywhere. Even in a SET statement.

Pobably Marcel wants to define complex expressions in a single spot, and use them in multiple expression fields in order to avoid having to copy-paste every change everywhere.

[stigchel]

You're right Peter, didn't realize that

[hugmarcel]

Exactly, you got the point. It must be done in script!

Marcel

[hugmarcel · Accepted solution]

The (simple) solution is to create a variable in script and use the variable in the formula. E.g. (dummy code):

let vServiceRequestFields = '';

for all fields in table 'Service Request'
  let vServiceRequestFields = vServiceRequestFields & '[' & field & ']=,';
next

SET vBudgetAmount = SUM({<$(vServiceRequestFields)>} DISTINCT Budgets)

Marcel