topic Re: Prior Year Max Month -1 in QlikView

Prior Year Max Month -1

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to get the value of max date of month of prior year -1.

Max Date in my qvw is 10/11/2016. Field name for date is Trans_Date

So, I want to get 9/30/2015

I gave my expression as =Date(monthend(addyears(Max(Trans_Date),-1))) this is returning 10/31/2015. I want to get 9/30/2015.

Any help greatly appreciated.

Re: Prior Year Max Month -1

sunny_talwar — Mon, 24 Oct 2016 18:35:38 GMT

May be try this:

=Date(MonthStart(AddYears(Max(Trans_Date),-1))-1)

Re: Prior Year Max Month -1

maxgro — Mon, 24 Oct 2016 18:44:10 GMT

=MonthEnd(AddMonths(Max(Trans_Date),-13))

Re: Prior Year Max Month -1

Mon, 24 Oct 2016 18:48:13 GMT

Thanks for the quick reply Sunny. It works

Just a quick qn, Now how about if I want to get 8/31/2015

Because my expression is KPI(Completed Month)-KPI(Completed Month -1) --- Current Year

For Prior Year Same expression but for Year -1.

So I have

vToDateCompletedMonth = Date(Monthend(Max(Link_Date),-1)) (Result - 9/30/2016)

vToDateCompletedPriorMonth = Date(Monthend(Max(Link_Date),-2)) (Result - 8/31/2016)

vToDateCompleteMonthPriorYear =Date(monthstart(addyears(Max(Link_Date),-1))-1) (Result - 9/30/2015)

vToDateCompletePriorMonthPriorYear ? (Expected result - 8/31/2015)

Hope my question makes sense..

Re: Prior Year Max Month -1

Mon, 24 Oct 2016 18:50:10 GMT

Thanks for the quick reply Maximo. But the expression won't work in all cases.

Re: Prior Year Max Month -1

sunny_talwar — Mon, 24 Oct 2016 18:59:37 GMT

May be this:

Date(monthstart(addyears(Max(Link_Date),-1), -1)-1)

Re: Prior Year Max Month -1

johnw — Mon, 24 Oct 2016 19:15:41 GMT

What cases? I do see one problem, which is that monthend() returns a timestamp rather than a date, but that can be fixed with a daystart(), and I doubt that's what you meant. I ran decades of possible dates through it, and I get the same results for that fixed expression, and Sunny's expression, and this:

monthstart(max(Trans_Date),-12)-1