Defining YTD/MTD Set Analysis expression through variables in Edit Script

[umartareen]

Hi All,

I have a scenario where I need to define my YTD expressions in the edit script using variables.

Here is what I am doing and I am not getting the output.

Variable / Expression :

LET vFLM_Amount    = 'sum({<[Fiscal Year] = {"$(vMaxYear)"}, [Period Number] = {">=$(vMinMonth) <=$(vMaxMonth)"}>} [Amount])';

where :

LET vMaxYear  = 'max([Fiscal Year])';

LET vMinMonth = 'MinString([Period Number])';

LET vMaxMonth = 'MaxString([Period Number])';

When I try the same expression in a text object, then it works except that I have to replace the double (") with single quotes ('), as the single quotes throw an error when used through edit script.

How can this be achieved ? Help will be appreciated !!

Thanks,

Umar

[adamdavi3s]

Hi Umar,

Don't use " (double quotes) use '' (double apostrophe)

LET vFLM_Amount    = 'sum({<[Fiscal Year] = {$(''vMaxYear)''}, [Period Number] = {''>=$(vMinMonth) <=$(vMaxMonth)''}>} [Amount])';

[sunny_talwar · Accepted solution]

May be try this:

LET vFLM_Amount = '=Sum({<[Fiscal Year] = {"$' & '(vMaxYear)"}, [Period Number] = {">=$' & '(vMinMonth) <=$' & '(vMaxMonth)"}>} [Amount])';

Dollar sign has the tendency to get evaluated in the script and in order to avoid that, break your expressions into parts and see if that works

[umartareen]

Hi

I worked the solutions provided and unfortunately they do not yirlf the output.

Thanks,

Umar

[adamdavi3s]

Actually I remeber this from before, try this:

LET vFLM_Amounttmp    = 'sum({<[Fiscal Year] = {~(''vMaxYear)''}, [Period Number] = {''>=$(vMinMonth) <=$(vMaxMonth)''}>} [Amount])'; 

LET vFLM_Amount   = "=replace([vFLM_Amounttmp],'~','$')";

[umartareen]

Yield*

[#7]

Hi Umar,

Might be it will useful to you.

LET vFLM_Amount = '=Sum({<[Fiscal Year] = {"$' & '(vMaxYear)"}, [Period Number] = {">=$' & '(vMinMonth) <=$' & '(vMaxMonth)"}>} [Amount])';

[sunny_talwar]

How about without the equal sign

LET vFLM_Amount = 'Sum({<[Fiscal Year] = {"$' & '(vMaxYear)"}, [Period Number] = {">=$' & '(vMinMonth) <=$' & '(vMaxMonth)"}>} [Amount])';

[umartareen]

Sunny, Thanks for the immediate responses ! It still doesn't work !!

Thanks,

Umar

[adamdavi3s]

This works for me, see attached

LET vFLM_Amounttmp    = '=sum({<[Fiscal Year] = {~(''vMaxYear)''}, [Period Number] = {''>=~(vMinMonth) <=~(vMaxMonth)''}>} [Amount])';

LET vFLM_Amount  = replace([vFLM_Amounttmp],'~','$');