8 Replies Latest reply: Dec 20, 2016 7:31 AM by Sunny Talwar

# Max : Difference between 2 dates

Hi

I want to create a text box that shows the order number that has the most days between order date and receipt date.  Also I require another text box to show the value as well.  So referring to the table below,  The one text box should display "ORD005" and another to display "38".

Thanking you in advance.

Kind regards

Nayan

 Order Number Order Date Receipt Date Days ORD001 05-Jan-16 25-Jan-16 20 ORD002 18-Jan-16 23-Jan-16 5 ORD003 27-Feb-16 07-Mar-16 9 ORD004 03-Mar-16 09-Mar-16 6 ORD005 08-Mar-16 15-Apr-16 38 ORD006 09-Apr-16 23-Apr-16 14 ORD007 05-May-16 14-May-16 9 ORD008 25-May-16 26-May-16 1 ORD009 14-Jun-16 03-Jul-16 19 ORD010 21-Jul-16 28-Jul-16 7 ORD011 09-Aug-16 29-Aug-16 20 ORD012 22-Sep-16 01-Oct-16 9
• ###### Re: Max : Difference between 2 dates

hi

use the firstsortedvalue function

FirstSortedValue([Order Number],-aggr([Receipt Date]-[Order Date],[Order Number]))

• ###### Re: Max : Difference between 2 dates

Thank you . It works.

If I want an answer where Order Date is greater than , say 1 Jun 2016, how will the formula work?

Kind regards

Nayan

• ###### Re: Max : Difference between 2 dates

the answer you look for is all the orders the order date is greater than 1 jun 2016

or the first order

• ###### Re: Max : Difference between 2 dates

Will the formula be as follows:

if([Order Date]>'1-Jun-16', FirstSortedValue([Order Number],-aggr([Receipt Date]-[Order Date],[Order Number])))

Kind regards

Nayan

• ###### Re: Max : Difference between 2 dates

no

if you want to calculate it only for orders after june 16

use this

FirstSortedValue({<[Order Date]={">=\$(=Date('01/06/2016','DD-MMM-YY'))}>}[Order Number],

-aggr(max({<[Order Date]={">=\$(=Date('01/06/2016','DD-MMM-YY'))}>}[Receipt Date]-[Order Date]),[Order Number])))

• ###### Re: Max : Difference between 2 dates

May be like this:

FirstSortedValue({<[Order Date] = {"=[Order Date] > MakeDate(2016, 6, 1)"}>} [Order Number],-([Receipt Date]-[Order Date]))

• ###### Re: Max : Difference between 2 dates

if you already have a field with the days evaluated you can also use

For OrderNumber

=only({< Days = {'\$(=Max(Days))'} >} OrderNumber)

For Days

=Max(Days)

• ###### Re: Max : Difference between 2 dates

Hi Vineeth

Unfortunately, I don't have the days evaluated.  If I do and let say I want the same answer, where order date is greater than 1 Jun 2016, how will the formula work?

Kind regards

Nayan