How to get MIN() value and its relative fields using expression?

[#1]

Hi All,

I have the following problem where I am not able to get the values for a minimum date value field for each customer.

I have a table as follows:

Customer	First Service Date	Next Service Date
1	09/01/2014	09/02/2014
1	09/02/2014	09/10/2014
1	09/10/2014	-
2	09/02/2014	09/14/2014
2	09/14/2014	-
3	09/10/2014	09/17/2014
3	09/17/2014	-

I have derived the minimum of 'First Service Date' for each customer using the expression:

=Date(Min([First Service Date], [Customer]), 'MM/DD/YYYY') but not able to get the corresponding 'Next Service Date' value.

Now I want to find the minimum of 'First Service Date' for each customer and then its corresponding 'Next Service Date'. I want the output as follows:

Customer	First Service Date	Next Service Date
1	09/01/2014	09/02/2014
2	09/02/2014	09/14/2014
3	09/10/2014	09/17/2014

Your inputs are appreciated.

Thanks!!!!!!

[Gysbert_Wassenaar · Accepted solution]

Try firstsortedvalue([Next Service Date],[First Service Date])

[MK_QSL]

FirstSortedValue([Next Service Date],Aggr(MIN([First Service Date]),Customer))

[#4]

Thank you!!!!