Set Analysis in Expression/Variable

[#1]

Hello All,

Could someone can explain me why this set analysis works in expression and doesn't work when I put it into a variable ?

My Script :

set test = chr(39)&'<'&max(Year)*10000&chr(39);

set SalesYear=Sum({$ <[Date]={$(=$(test))}>} [Sales]);

In my chart when I use directly my expression of "SalesYear" it works fine, but when I use the variable "SalesYear" I've only 0 values.

When I put my variable in text object It displays this :

=Sum({$ <[Date]={}>} [Sales])

I don't understand what happen in my variable.

Have a good day,

[Gysbert_Wassenaar · Accepted solution]

set SalesYear=Sum({$ <[Date]={$(=$(test))}>} [Sales]);

$(test) will be dollar-expanded in the script. Since test is not defined the result is nothing. And then $( ...nothing... ) is expanded too so the result is that SalesYear will have the value Sum({$ <[Date]={}>} [Sales]).

What you can do instead is this:

set SalesYear=Sum({$ <[Date]={@(=@(test))}>} [Sales]);

let SalesYear=replace('$(SalesYear)','@','$');

Or this:

LET SalesYear = 'Sum({$ <[Date]={$' & '(=$' & '(test))}>} [Sales])';

[#3]

Thank You,

Your answer is very helpful. You've right :

Since test is not defined the result is nothing.

At first I would prefer the second solution, because you define your expression directly.

But, if you want to had some restrictions you'll have to had chr(39) everyway.

Example, add "City" restriction:

LET SalesYear = 'Sum({$ <[Date]={$' & '(=$' & '(test))}, [City]={' & chr(39) & 'Washington' & chr(39) & '}>} [Sales])';

Is interpreted as :

Sum({$ <[Date]={$(=$(test))}, [City]={'Washington'} >} [Sales])';

So I prefer the first way which is more readable :

set SalesYear=Sum({$ <[Date]={@(=@(test))} , [City]={'Washington'} >} [Sales]);

let SalesYear=replace('$(SalesYear)','@','$');

Thank you for your help.