Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ettienero
Contributor
Contributor

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

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

4 Replies
Neymar_Jr
Creator II
Creator II

Hi

Try using Sum($(MTD) Sales)

Thanks,

RT

ettienero
Contributor
Contributor
Author

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
Partner - Creator III
Partner - Creator III

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein