topic Re: MTD in QlikView

MTD

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

Hi Experts,

Can any one please help me to exclude month and Year fields from the below set expression of MTD.

sum({<Year=,month=,
[Order Date]= {">=$(=Date(MonthStart(max([Order Date])),'MM/DD/YYYY')) <=$(=Date(max([Order Date]),'MM/DD/YYYY'))"}>}Sales)

Here i applied Year= and month= but i am unable to exclude month and year.Still selections are applied when i select 2012,2013...years

Please help me on this.

Thanks in advance.

Re: MTD

tresB — Fri, 21 Dec 2018 06:40:32 GMT

You need to include this exclusion in max() being used in the expression as well, like:

sum({<Year=,month=,[Order Date]= {">=$(=Date(MonthStart(max( {<Year=,month=>} [Order Date])),
'MM/DD/YYYY')) <=$(=Date(max({<Year=,month=>} [Order Date]),'MM/DD/YYYY'))"}>}Sales)

Re: MTD

tresB — Fri, 21 Dec 2018 07:30:09 GMT

Try like:

sum({<Year=,month=,
[Order Date]= {">=$(=Date(MonthStart(max({<Year=,month=>} [Order Date])),'MM/DD/YYYY')) <=$(=Date(max( {<Year=,month=>} [Order Date]),'MM/DD/YYYY'))"}>}Sales)

Re: MTD

albert_guito — Fri, 21 Dec 2018 07:33:42 GMT

HI,

If you select a month or a year your [Order Date] will refer to this period.

If you need the current MTD you have to change the Order Date by Today() in the set analysis

sum({<Year=,month=,
[Order Date]= {">=$(=Date(MonthStart(Today())),'MM/DD/YYYY')) <=$(=Date(Today(),'MM/DD/YYYY'))"}>}Sales)

Ag+