topic Re: Earliest date across fields in QlikView

Earliest date across fields

dawgfather — Wed, 25 Nov 2020 16:16:04 GMT

I have several fields that might be populated with a variety of dates and I need to determine the earliest of them all.

I might have a Lease End date, a maintenance end date, or and end of life date.

Excel does that with something like =MIN(LeaseEndDate, MaintEndDate, EOLDate)

How can I do this in QlikView?

Some sample data would look like the below:

Model	LeaseEndDate	MaintEndDate	EOLDate	Earliest
DMX4	6/29/2013	-	9/30/2017	6/29/2013
NS480	6/30/2015	-	12/31/2016	6/30/2015
NS40	6/30/2015	-	7/31/2014	7/31/2014
USPVM	OWNED	1/31/2016	9/30/2016	1/31/2016
FAS2050A	OWNED	-	6/30/2016	6/30/2016
FAS2050A	OWNED	-	6/30/2016	6/30/2016

Re: Earliest date across fields

trdandamudi — Fri, 29 Apr 2016 20:41:35 GMT

May be like attached:

Re: Earliest date across fields

santiago_respane — Fri, 29 Apr 2016 20:42:32 GMT

Hi Scott,

according to your sample data in the "Earliest" column you have the earlier date of all columns, so doing a min('Earliest') will give you the value that you want.

But if you have another date fields in other tables you can do as follows:

=RangeMin(Min(LeaseEndDate),Min(MaintEndDate),Min(EOLDate),Min(Earliest),Min(AnotherDateField),.....)

Please let me know if this helps.

Kind regards,

Re: Earliest date across fields

dawgfather — Mon, 02 May 2016 14:01:47 GMT

Apologies...that "Earliest Date" was what I expect to see as a result of the expression.

Your RangeMin expression seems to give me that.

Thank you!