Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be try this:
=Date(MonthStart(AddYears(Max(Trans_Date),-1))-1)
=MonthEnd(AddMonths(Max(Trans_Date),-13))
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..
Thanks for the quick reply Maximo. But the expression won't work in all cases.
May be this:
Date(monthstart(addyears(Max(Link_Date),-1), -1)-1)
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