topic Re: last 13 months up to user selection in QlikView

last 13 months up to user selection

rmuhammad — Tue, 10 Oct 2017 20:46:18 GMT

Hi All

what would be set analysis statement for selecting last 13 month from the select Year and month. Please see attached image what I want to do.

Thanks

Rashid

Re: last 13 months up to user selection

Anonymous — Wed, 11 Oct 2017 04:56:31 GMT

hi rmuhammad

try this expression dis will work acoording to ur feilds .

=Sum({<OrderDate={">=$(=(MonthStart(Max(OrderDate),-12)))<=$(=MonthEnd(Max(OrderDate),-12)"},Year=,Month=>} [Opportunity Amount])

Re: last 13 months up to user selection

tresB — Wed, 11 Oct 2017 05:22:11 GMT

Something like:

Count( {<YEAR, MONTH, Period={">=$(=Date(MonthStart(Max(Period),-13))) <=$(=Date(Max(Period)))"}>} Measure)

Edit: removed redundant addmonths()

Re: last 13 months up to user selection

prma7799 — Wed, 11 Oct 2017 05:46:20 GMT

Hi tresesco‌,

Why we can't use Addmonths here??

like this MonthStart(AddMonths(max([Date]),-13))

Re: last 13 months up to user selection

tresB — Wed, 11 Oct 2017 05:53:38 GMT

You can. But when you get the same output with a less function, why overkill?

MonthStart(AddMonths(max([Date]),-13))

and

MonthStart(max([Date]),-13)

return the same output.

Re: last 13 months up to user selection

rmuhammad — Wed, 11 Oct 2017 13:14:04 GMT

Hi Tresesco

Thank you for your help. Can you please just break down statement in portion and explain what portion is doing what? So me and others new comers can learn from it.

Count( is doing count of MEASURE

>{<YEAR, MONTH, ????

Period={">=$(=Date(MonthStart(Max(Period),-13))) ????? -13 is period 05-2016, so its period is greater then 05-2016. what is $(=Date(MonthStart(Max doing here?

<=$(=Date(Max(Period)))"}>} ???? period selection is 05-2017, so it is less then equal to 05-2017 or what ever is selected

Measure) is measure