FirstSortedValue and dates

[patrickbender]

Hi i have been at this for a few hours now,

I just want to use a set analysis with FirstSortedValue formula with a input field where I can write the date formatted as Date2.

For some reason using vAsofDate works just fine but when using Date2 it doesn't.

I have created 2 new variables just to check if QV can read both dates as numbers and it seems that it can.

I'm confused.

FirstSortedValue({<StartDate ={"<=$(vAsofDate)"}>}Name,-EndDate)

vAsofDate	4/3/2014	The original comparison date
Date2	2017-12-31	The date i want to use (in this format)
Date 1 Num	41 732	The original date formated as number
Date 2 num	43 100	The new date formated as number

Table with dates

EMPID	Name	StartDate	EndDate
1	A	2015-03-25	2015-10-11
1	G	2016-03-25	2016-10-11
2	B	2014-02-02	2015-05-07
3	C	2013-01-01	2014-01-01
4	D	2012-01-01	2014-01-01
5	E	2010-05-27	2015-12-31
6	F	2012-02-02	2014-02-02

Table using original date

EMPID	FirstSortedValue({<StartDate ={"<=4/3/2014"}>}Name,-EndDate)
	E
2	B
3	C
4	D
5	E
6	F

Table using new date:

EMPID	FirstSortedValue({<StartDate ={"<=2017-12-31"}>}Name,-EndDate)

[sunny_talwar · Accepted solution]

The date format is different... may be try this

FirstSortedValue({<StartDate ={"<=$(=Date(Date#(Date2, 'YYYY-MM-DD'), 'M/D/YYYY'))"}>}Name,-EndDate)

[patrickbender]

Thank you so much!

Would you like to explain to me why i need to convert Date2 that seems to be formatted the same way as the other dates? It feels a bit backward?