How to Convert this Set Expression in Qlikscriptset

[anindya_manna]

MTD:sum({$<[Process Date]={">=$(=MonthStart(Max([Process Date]))) <=$(=Max([Process Date]))"}, [Week ID]=>}[Sales Amt])

How to convert this above set expression in qlikscript so that I can write in  load script??

[Anil_Babu_Samineni]

I don't prefer this to use in script and that we don't have same in script..

[tresesco]

Set analysis (used in your expression) is used to control selection in the front-end. In script, you don't have the scope of knowing about selection, so you can't handle it there. That means, set analysis-based expression can't be replicated in script. Hence, it is not feasible and not recommended too, because calculations in the back-end reduces the flexibility of analysis; once the number is calculated in script, that is static; you can't calculate them in different granularity or filters that were not considered in script.

And yes, if you wish to calculate the number irrespective of selection consideration and against a dimension or on overall data set, that would be possible to do in the script. However, the the question of how effective that would be still persists.

[tomasz_tru]

The question is: is it about static MTD flag or not?

[rahulpawarb]

As a workaround you can create a summary table with MTD sales amount values (refer below sample script).

SummaryTable:

LOAD

          Year([Process Date]) AS Year,

          Month([Process Date]) AS Month,

          Sum([Sales Amt]) AS MTD

Resident SalesTable

GROUP BY

Year([Process Date]),

Month([Process Date]);

P.S.: Data available in the SummaryTable will at month level granularity only.

Regards!

Rahul

[tresesco]

Yes, indeed. And if so, Anindya might want to use InMonthToDate() in the script.