topic Re: How to get MIN() value and its relative fields using expression? in App Development

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

Wed, 17 Sep 2014 13:00:38 GMT

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!!!!!!

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

Gysbert_Wassenaar — Wed, 17 Sep 2014 13:02:24 GMT

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

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

MK_QSL — Wed, 17 Sep 2014 13:03:20 GMT

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

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

Thu, 18 Sep 2014 18:47:27 GMT

Thank you!!!!