How to show rows which have maximum date?

[#1]

Hi,

I have input data in SQL table as below

ID	EntryNo	Months	Sales	Enter Date	Leave Date	Total
123242	605491	5	150	26/08/2012	26/12/2012	750
123242	605492	1	0	26/01/2013	26/01/2013	0
123242	605493	10	150	26/02/2013	26/11/2013	1500
123242	605494	1	0	26/12/2013	26/12/2013	0
123242	605495	6	150	26/01/2014	26/06/2014	900
123242	605496	1	0	26/07/2014	26/07/2014	0
123242	605497	1	150	26/08/2014	26/08/2014	150
123242	605498	8	0	26/09/2014	26/04/2015	0
123242	605499	14	160	26/05/2015	26/06/2016	2240
123242	605500	8	150	26/07/2016	26/02/2017	1200
123242	605501	10	252	26/03/2017	26/12/2017	2520
123242	605502	7	262	26/01/2018	26/07/2018	1834
123242	605503	9	160	26/08/2018	26/04/2019	1440
5354353	296080	1	782	25/10/2006	25/10/2006	782
5354353	296081	3	411	25/11/2006	25/01/2007	1233
5354353	296082	9	362	25/02/2007	25/10/2007	3258
5354353	296083	34	484	25/11/2007	25/08/2010	16456
5354353	296084	12	250	25/09/2010	25/08/2011	3000

I need to show output data table in qvw as below

ID	Sales	Leave Date
123242	160	26/04/2019
5354353	250	25/08/2011

so, I need to show the rows only have max Leave Date for each ID

I have used below expression but it is not showing data and showing blank chart.

=FirstSortedValue([Leave Date],-ID)

Please can anyone suggest me how to do this.

[maxgro · Accepted solution]

try

FirstSortedValue(Sales, -[Leave Date])

[#3]

may be I need to use below

=FirstSortedValue([Leave Date],-[Enter Date])

[#4]

Thanks. This works for Sales.

[maxgro]

or this (you said by leave date)

FirstSortedValue([Leave Date], -[Leave Date])

[#6]

Thanks.