Variable interpreted in script

[#1]

Hello,

I have the following Set Analysis expression which I store in a variable in the script (it is not supposed to be modified so I do not want it defined only in Variable Overview):

SET SetAnalysis = Count({$<CODE = {'LAST'}, DATE = {">= $(=Min(CalendarDate))"} * {"<= $(=Max(CalendarDate))"}>} DISTINCT ISSUE]);

The issue is that when the script runs, it tries to interpret $(=Min(CalendarDate)) and $(=Max(CalendarDate)) despite the SET but Min() and Max() do not work in script so it fails (Graph expression only).

How can I make sure script execution does not try to intepret $(=Min(CalendarDate)) and $(=Max(CalendarDate)) (and fail).

I have tried a few modifications without success to make it look like a string:

SET SetAnalysis = Count({$<CODE = {'LAST'}, DATE = {">= $(=Min(CalendarDate))"} * {"<= $(=Max(CalendarDate))"}>} DISTINCT ISSUE]);
SET SetAnalysis1 = 'Count({$<CODE = {"LAST"}, DATE = {">= $(=Min(CalendarDate))"} * {"<= $(=Max(CalendarDate))"}>} DISTINCT ISSUE])';
SET SetAnalysis2 = "Count({$<CODE = {'LAST'}, DATE = {'>= $(=Min(CalendarDate))'} * {'<= $(=Max(CalendarDate))'}>} DISTINCT ISSUE])";

Is that possible?

[jjordaan]

This is working I my script.

SET SetAnalysis2 = 'Count({$<CODE = {"LAST"}, DATE = {">= $(=Min(CalendarDate))"} * {"<= $(=Max(CalendarDate))"}>} DISTINCT ISSUE])';

Can you send me a example zo I can look into it?

[#3]

Here is an example. Data do not make much sense, it is just for test purpose. You basically are supposed to Sum COST for Products 3 to 8 (= 10).

swuehl proposal works fine. I initially switched some '/" to his solution because I did not expect DATE = {">= ' to be properly parsed but in fact it does.

I was hoping for a lighter/cleaner version but I guess I do not have a choice.

Since those Set Analysis expressions are not supposed to be modified, I find it cleaner to declare them in the script instead of the Variable Overview where I would only store variables meant to be modified and retrieved from one session to another.

[flipside]

Although I haven't fully tested it, one solution which seems to work with your example is to replace the code you need to defer dollar expansion with a parameter holder and then submit the code at run time. Using your sample, here's how it works ...

Your target code is ...

Set SAflipside = Sum({$<CODE={"Y"}, PRODUCT = {">= $(=Min(MIN))"} * {"<= $(=Max(MAX))"}>} SOLD)

... but you need to defer the Min and Max calculations. Replace ALL the PRODUCT code restriction with a holder ($1) so it becomes ...

Set SAflipside = Sum({$<CODE={"Y"}, PRODUCT = $1>} SOLD)  - it will show an error in the variable list (red underline), but it works.

... then in your front end object, resubmit it as this, the $1 holder is replaced by the submitted code in bold ...

$(SAflipside({">= $(=Min(MIN))"} * {"<= $(=Max(MAX))"}))

flipside

[#5]

Thanks flipside but it is important to me to only have one variable call in the front end objects.

In case you need change a field name or adjust a formula, updating it everywhere is too painfull.

But it looked like a good trick!

[flipside · Accepted solution]

Technically it is still only one variable call and refining it the following way may be of more interest as you can control the parameters from within the script to make global changes easier.

SET SAflipside3 = Sum({$<CODE={"Y"}, PRODUCT = {'>=$1'} * {'<=$2'}>} SOLD);
SET p1= =Min(MIN);
SET p2= =Max(MAX);

then use the call as ... 

$(SAflipside3($(p1),$(p2)))

flipside

[#7]

It works perfectly flipside.

swuehl solution works fine too but I find the variable call more elegant from a code a point of view.

Thanks a lot guys,

jc