Discussion Board for collaboration related to QlikView App Development.
Hi,
I need to compare results of current quarter and previous quarter (I use set analysis) and I need to extract whole date from quarter..
I know how to get max month from previous quarter -> previous_quarter_month =Month(QuarterEnd(maxdate))-3
(maxdate is variable max(date))
but I do not know how to extract also the last day of the month..because if I use day_previous_quarter =day(MonthEnd(previous_quarter_month)) it returns bad value (e.g. 31 even if the month is June which has just 30 days..)
In set analysis I need to define month and day separately.
Any idea how to find out the whole date of the last day of the previous quarter?
Thank you so much.
Nela
The problem is with the function month, this function does not return a date, but only the month. MonthEnd however expects a date.
So for day_previous_quarter try use
=day(QuarterEnd('$(=Addmonths(Max(Date),-3))'))
Also mind the quotes
The problem is with the function month, this function does not return a date, but only the month. MonthEnd however expects a date.
So for day_previous_quarter try use
=day(QuarterEnd('$(=Addmonths(Max(Date),-3))'))
Also mind the quotes
You can use a second argument to function quarterend() to indicate previous quarter:
=QuarterEnd(today(),-1)
Or
=QuarterEnd(Max(Date), -1)
This will return a timestamp, use floor if you want to get the integer:
=Date(Floor(QuarterEnd(max(Date),-1)) )
Or to get the Month or Day:
=Month(QuarterEnd(max(Date),-1))
=Day(QuarterEnd(max(Date), -1))
DayName(QuarterEnd(maxdate),-1)
Thank you so much, it works