Create Variable using Script and use it in the expression

[chavanqlik]

Hello All,

I'm trying to create a variable using Load Script editor, and use that value in expressions.

I'm getting the syntax error for this,

Let vSetMaxServDischMonth=[Month and Year]={"$(=max({1}[Month and Year]))"};

But when I create the same expression using expression editor and creating the variable and adding the expression, works fine.

Could anyone please provide your insights on this, really appreciate.

Thanks

[dplr-rn]

aggregation like you have defined doesnot work in the script.
if you absolutly have to do it in script and not in variable section. you should do something like below

temp-table:
select Max([Month and year]) as MaxMonthYear resident YourTable;
let vMaxMonthYear = peek('MaxMonthYear');
drop table temp-table;

[rwunderlich · Accepted solution]

You are trying to create a literal value so you should use SET instead of LET 

Set vSetMaxServDischMonth=[Month and Year]={"$(=max({1}[Month and Year]))"};

If you wanted to create a literal with Let the string should be quoted as:

Let vSetMaxServDischMonth='[Month and Year]={"$(=max({1}[Month and Year]))"}';

However, you will run into problems as the "$(" will be interpreted in the script. So you must use Let with some kind of escaping of the $( like:

Let vSetMaxServDischMonth=replace('[Month and Year]={"@(=max({1}[Month and Year]))"}', '@', '$');

-Rob

[chavanqlik]

Thank you 🙂