Declare string for MTD and YTD in load script to use in set analysis

[ettienero]

Hi

I have several expressions using set analysis to determine sum of sales for periods MTD, YTD, etc. This is an example of one:

Sum({<Year= ,Month= ,Day= ,Week= ,[Week day] = ,Date={">=$(=MonthStart(vMaxDate))<=$(vMaxDate)"}>} Sales)  

I've been trying to declare the "set analysis" portion in the load script:

SET [MTD] = '{<Year= ,Month= ,Day= ,Week= ,[Week day] = ,Date={">=$(=MonthStart(vMaxDate))<=$(vMaxDate)"}>}'

to use as a variable in the expressions like this: Sum($([MTD]) Sales)

, but this is not working. 

Would this be possible to do? and if so, can anybody please help with the correct syntax, etc.?

[Neymar_Jr]

Hi

Try using Sum($(MTD) Sales)

Thanks,

RT

[ettienero]

Hi

I did that, but still not getting an answer. Looking at the variable in the model, the text result looks like this:

{<Year= ,Month= ,Day= ,Week= ,[Week day]= ,Date={">=<="}>}

it is missing the DSEs in the Date section. How do I do the SET declaration in the load script correctly? This one seems to exclude some details:

SET [MTD] = '{<Year=, Month= ,Day= ,Week= ,[Week day] = ,Date={">=$(=MonthStart(vMaxDate))<=$(vMaxDate)"}>} '

[Uday_Pasupuleti]

Try this

YeartoDate(DateField)*-1 as CurYTDFlag,

If(DateField >= MonthStart(Today(),0) and DateField <= Today(), 1, 0) as MTDFlag

In your exp use Sum({<MTDFlag ={1}>}Sales) 

                                Sum({<CurYTDFlag={1}>}Sales)

[jonathandienst]

The $ expansions do not work when the Set is evaluated. You only need this expansion to happen in the front end, not during the load script, when these expansions will return nulls.

Using Set does NOT prevent $ expansion as it happens before the SET line executes.  The same with a LET statement.

You need to use deferred expansion of the $() expressions. You can do this a number of ways, and either of these will work. The choice is a question of style and taste:

Set MTD = {<Year= ,Month= ,Day= ,Week= ,[Week day] = ,Date={">=#(=MonthStart(vMaxDate))<=#(vMaxDate)"}>};
Let MTD	= Replace(MTD, '#', '$');

or

Let MTD = '{<Year= ,Month= ,Day= ,Week= ,[Week day] = ,Date={">=$' + '(=MonthStart(vMaxDate))<=$' + '(vMaxDate)"}>}';

Both of these prevent the Set or Let from evaluating the $ expansion