topic Re: Declare string for MTD and YTD in load script to use in set analysis in App Development

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

ettienero — Tue, 07 May 2019 11:38:19 GMT

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.?

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

Neymar_Jr — Tue, 07 May 2019 12:02:18 GMT

Try using Sum($(MTD) Sales)

Thanks,

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

ettienero — Tue, 07 May 2019 12:23:59 GMT

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)"}>} '

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

Uday_Pasupuleti — Tue, 07 May 2019 12:38:16 GMT

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)

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

jonathandienst — Tue, 07 May 2019 13:32:19 GMT

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, '#', '$');

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

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