topic Re: How to Convert this Set Expression in Qlikscriptset in QlikView

How to Convert this Set Expression in Qlikscriptset

anindya_manna — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to Convert this Set Expression in Qlikscriptset

Anil_Babu_Samineni — Wed, 20 Sep 2017 08:30:07 GMT

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

Re: How to Convert this Set Expression in Qlikscriptset

tresB — Wed, 20 Sep 2017 08:47:18 GMT

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.

Re: How to Convert this Set Expression in Qlikscriptset

tomasz_tru — Wed, 20 Sep 2017 08:59:40 GMT

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

Re: How to Convert this Set Expression in Qlikscriptset

rahulpawarb — Wed, 20 Sep 2017 09:00:34 GMT

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

Re: How to Convert this Set Expression in Qlikscriptset

tresB — Wed, 20 Sep 2017 09:20:28 GMT

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