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]

Hi cc cc,

You should place the code between ''.

[swuehl]

You need to prevent QV from evaluating the dollar sign expansion, either by doing something like

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

Or substituiting the $ char with some other, e.g. @ and doing a replace on your variable in a second statement.

[israrkhan]

have you tried " " around your expression like below:

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

i think it will consider it a whole string and will not try to calculate expression.

i did not tried but seems to work..

Khan

[#5]

@Jeoren:

I tried

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

It fails:

Count({$<CODE = {'LAST'}, DATE = {'>= (internal error)'} * {'<= (internal error)'}>} DISTINCT ISSUE)

[jjordaan]

I mean single quotes

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

[#7]

I tried the following:

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

Added double quotes around so it does not interpret Count() and replaced all others by single quotes but when I check SetAnalysis after execution, it is empty so I guess something goes wrong.

[#8]

Did not work

Variable Overview:

'Count({$<CODE = {'LAST'}, DATE = {'>= (internal error)'} * {'<= (internal error)'}>} DISTINCT ISSUE)'

I did not expect the parser to be able to handle opening/closing quotes but it looks like it can.

I do not know difference between ' and " (apart from syntax highlighting) and if it makes a real difference compared to PHP or other languages.

[swuehl]

This one works for me ( you need to handle also the quoted LAST field modifer value):

LET SetAnalysis = '=Count({$<CODE = {'&chr(39)&'LAST'&chr(39)&'}, DATE = {">= ' & '$' & '(=Min(CalendarDate))<= ' & '$' & '(=Max(CalendarDate))"}>} DISTINCT ISSUE])';

edit: Or use the replace method:

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

LET SetAnalysis2 = replace('$(SetAnalysisTmp)','@','$');

[tresesco]

You have to use Replace() intelligently, I am just copying one section of discussion from here by @jacob drummond. I am sure this would help you.

What you can do however is define the string you want in the script and use some other special character such as ~ to take the place of the $. Define a second variable which then references the first which replaces the ~ character with a $.

set vTodayString = "~(Today());

set vToday = "=replace([vTodayString],'~','$')";

It's a hideous way of doing it, but it should work. I did this when using a variable to store an expression containing set analysis, which used dollar expansion within the definition for the set. Letting the script do the dollar expansion was generating an "internal error", hence this approach.

I can't see why you would want to do this for such a simple function, but it should help others out who may be doing something a little more complex.

Here is my set analysis expression for example:

// Set analysis string using ~ in the place of $ to prevent expansion

set DailyDemandString = "sum({~<TransType = {~(SALESTRANS)}, PERIOD_DATE = {'>=~(=Date('~(Today)' - [NumberOfDaysForDemand]))<=~(Today)'}>} MO_Quantity * -1) / [NumberOfDaysForDemand]";

set DailyDemand = "=replace([DailyDemandString],'~','$')";