Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

FirstSortedValue and dates

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)

vAsofDate4/3/2014 The original comparison date
Date22017-12-31 The date i want to use (in this format)
Date 1 Num41 732 The original date formated as number
Date 2 num43 100The new date formated as number

Table with dates

EMPID Name StartDate EndDate
1A2015-03-252015-10-11
1G2016-03-252016-10-11
2B2014-02-022015-05-07
3C2013-01-012014-01-01
4D2012-01-012014-01-01
5E2010-05-272015-12-31
6F2012-02-022014-02-02

Table using original date

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

Table using new date:

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

Accepted Solutions
Highlighted

Re: FirstSortedValue and dates

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

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

View solution in original post

2 Replies
Highlighted

Re: FirstSortedValue and dates

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

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

View solution in original post

Highlighted
Contributor III
Contributor III

Re: FirstSortedValue and dates

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?