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

[sunny_talwar]

Just tried mine on adamdavi3s‌'s sample. Seems to work as well

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

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

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

[adamdavi3s]

It's personal preference I think.... I like the two step process as I think for bit formula is is easier to understand than using extra &

[sunny_talwar]

adamdavi3s‌ - You are absolutely right . I just wanted to point out that the other solution also works, But yes, the OP and other people who visit this thread are free to choice whichever they think is a better option.

Best,

Sunny

[adamdavi3s]

Oh totally, I couldn't agree more!

variety is the spice of life and its great to have options!!

[sunny_talwar]

How is this different then what I have already proposed my friend?

[umartareen]

Hi All,

Thanks for your replies, appreciate it !

Not sure why the solutions provided in the forum are not working for me. It could be that I am going wrong somewhere while applying them ! Or could there be a version issue ? Just curious.

If nothing else works, I will have to make these variables on my front end !

Regards,

Umar

[sunny_talwar]

Umar -

May be if you can replicate the same issue in a sample file and share it with us? That ways we can actually see the issue and will know what might be going wrong. Right?

[umartareen]

Sunny, your solution works perfectly. Its actually a data issue on my end and so was not getting the output.

Thank you very much,

Regards,

Umar