9 Replies Latest reply on Aug 24, 2016 10:57 PM by Prasenjeet Acharjee

# date difference between multiple dates (same column) for each id

Hi,

I am stuck with a problem and seeking a solution. I have a Column  having sales order identifier and another column having revenue recognition date. Now, each sale order might have multiple revenue recognition dates. I want to compute the number of days between each instance of revenue recognition ( from column revenue recognition date) for a each sale order.

Can i get any help on this query of mine?

• ###### 1. Re: date difference between multiple dates (same column) for each id

Hello Prasenjeet

Have you tried the LOOKUP() function? it's a table calculation so you'll need to specify the "compute using" direction (table down if your revenue recognition dates are on the rows shelf).

Your calculation might look something like:

DATEDIFF('day', [recognition date], LOOKUP([recognition date], -1))

Hope that helps, if you can't get your answer from that maybe share a sample of your data so we can help further.

Thanks

Steve

• ###### 2. Re: date difference between multiple dates (same column) for each id

Hi steve,

I tried the approach you suggested. But I am still not getting the desired result. Attaching the workbook for your reference.

• ###### 3. Re: date difference between multiple dates (same column) for each id

Each sale order no. might have multiple revenue recognition dates. I want the difference between the dates (in no of days) per sale order identifier.

• ###### 4. Re: date difference between multiple dates (same column) for each id

OK, just so I understand what you're after, looking at the table below,

For 315104006 - as there is only 1 date, this should return 0 for date diff?

and for 315104010, this should return 7

and for 315104013, this should also return 7 (diff from first to last date for the single Sales Order Identifier)?

Thanks

Steve

• ###### 5. Re: date difference between multiple dates (same column) for each id

Assuming I've understood that correctly, here is a solution.

Using LOD to get the count of dates per sales order identifier and the min and max dates for each one:

Hope that works for you, I haven't attached the workbook as it's quite large but here is the above calculation so you can copy & paste into a calculated field in your workbook:

//when there's only 1 date for a sales order, return 0

IF {FIXED [Sales Order Identifier] :

COUNTD([Revenue Recognition Calendar Date DD MM YYYY Code])}

= 1

THEN

0

//If there's more than 1 date, return diff between min and max dates

//per sales order

ELSEIF {FIXED [Sales Order Identifier]

: COUNTD([Revenue Recognition Calendar Date DD MM YYYY Code])}

> 1

THEN DATEDIFF('day',

//Min date for sales order

{FIXED [Sales Order Identifier] :

MIN([Revenue Recognition Calendar Date DD MM YYYY Code])}

,

//Max date for sales order

{FIXED [Sales Order Identifier] :

MAX([Revenue Recognition Calendar Date DD MM YYYY Code])}

)

ELSE NULL END

Steve

• ###### 6. Re: date difference between multiple dates (same column) for each id

Hi Steve,

Let me explain my issue a bit more. Taking the example you shared:-

For 315104006 - as there is only 1 date, this should return 0 for date diff?    Yes

and for 315104010, this should return 7   -   Yes

and for 315104013, this should also return 7 (diff from first to last date for the single Sales Order Identifier)?  for this i wish to get 6 (diff between 19/11/2015 and 25/11/2015) and 1 (diff between 25/11/2015 and 26/11/2015).  This (difference between each revenue recognition date per sales order) is important as I am trying to investigate possible fraud cases.

Is it possible to attach the workbook as well using a sample of the data that i shared?

• ###### 7. Re: date difference between multiple dates (same column) for each id

Here is the workbook with the method I used above (which doesn't work for item 3 in the list)

I'll have a go at finding another solution.

1 of 1 people found this helpful
• ###### 8. Re: date difference between multiple dates (same column) for each id

Hello again

I think this next version solves the problem but for this to work I believe you'll need to show the date in the worksheet and with a large amount of data it could run quite slowly:

Workbook attached.

Steve

1 of 1 people found this helpful
• ###### 9. Re: date difference between multiple dates (same column) for each id

Thank you Steve for saving my day.