FIRSTSORTEDVALUE not working with date

[matthewgraci]

I have a test table with two columns:   Servers and ServersDate.   Loading date from CSV using:

Date(Date#([ServersDate],'MM/DD/YYYY hh:mm'),'MM/DD/YYYY') as ServersDate1

Servers,ServersDate1

23,04/01/2020

23,04/02/2020

24,04/03/2020

24,04/04/2020

I am trying to find the Server count for the oldest date:

=round(firstsortedvalue([DISTINCT] servers, ServersDate1)

This returns a null.

I have confirmed that ServersDate1 is a valid date by going into Number tab and changing it to an integer and it displayes incrementing numbers.

Cannot figuring out what I am doing wrong.

Thanks.

[HirisH_V7]

Your Date transformation field should be this, remove hh mm.

Date(Date#([ServersDate],'MM/DD/YYYY'),'MM/DD/YYYY') as ServersDate1

[matthewgraci]

When I remove 'hh:mm' the date doesnt display at all.

[HirisH_V7]

But in date mentioned in u r  query doesn't have time in it. so your date# snippet should not have that.

PFA for ref

[matthewgraci]

I am sorry for the confusion.   The date in the CSV file is:  "MM/DD/YYYY hh:mm".   I am converting it to date because if I dont it comes in as a string.

Thanks

[HirisH_V7]

Ok then you need servers based on oldest time stamp or date. Based on that we can convert this...

To Extract only Date&Time:

Timestamp(Timestamp#([ServersDate],'MM/DD/YYYY hh:mm'),'MM/DD/YYYY hh:mm') as ServersDateTime

To Extract only Date:

Date(Timestamp#([ServersDate],'MM/DD/YYYY hh:mm'),'MM/DD/YYYY') as ServersDate

[matthewgraci]

I changed it to timestamp in the load.  FirstSortedValue still is null.

Is there anything else I could be missing?   Seems like a straightforward dataset for this function?

Thanks.