topic Re: How to get date (last day of prev. quarter) from quarter? in QlikView

How to get date (last day of prev. quarter) from quarter?

Wed, 26 Aug 2015 13:04:32 GMT

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

Re: How to get date (last day of prev. quarter) from quarter?

stigchel — Wed, 26 Aug 2015 14:05:30 GMT

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

Re: How to get date (last day of prev. quarter) from quarter?

swuehl — Wed, 26 Aug 2015 14:13:32 GMT

You can use a second argument to function quarterend() to indicate previous quarter:

=QuarterEnd(today(),-1)

=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))

Re: How to get date (last day of prev. quarter) from quarter?

MarcoWedel — Wed, 26 Aug 2015 14:56:30 GMT

DayName(QuarterEnd(maxdate),-1)

Re: How to get date (last day of prev. quarter) from quarter?

Thu, 27 Aug 2015 06:42:17 GMT

Thank you so much, it works