5 Replies Latest reply on Feb 7, 2014 12:30 PM by Aaron Clancy

How can I calculate the difference in dates along a single column of dates?

I have multiple Distinct CustomerIDs each with a set of TransactionDates. These transaction dates are one column and I want to calculate the difference between transaction dates for each CustomerID to ultimately come up with Transaction Cycle Time.

I can attach an example file, but below is a rough look at the data I have.

CustomerID                         TransactionDate               DateDiff

1                                        1/1/2014

1/3/2014                          2

1/1/2014

2                                       1/4/2014                         3

1/10/2014                       6

It's this DateDiff field that I ultimately want to calculate.

• 1. Re: How can I calculate the difference in dates along a single column of dates?

This approach is with superstore data, where there are multiple order dates for a customer.

Let me know if it's close to what you're looking for.

P.S.  If you don't need to show the different transactions and all you care about is the datediff then the logic is much easier.

Customer        Datediff

A                         3

B                         25

C                        15

D                        22

This is the approach with the layout that you requested:

Calc:

Compute Using:

• 2. Re: How can I calculate the difference in dates along a single column of dates?

That looks great. One more thing...my goal is to find the average customer cycle time (time between purchases).

So the total sum of days is close, but can you take the average number of days between each purchase and come up with a table average?

Thanks!

• 3. Re: How can I calculate the difference in dates along a single column of dates?

You'd add a    /size()   to the calc:

• 4. Re: How can I calculate the difference in dates along a single column of dates?

Thanks a ton!

• 5. Re: How can I calculate the difference in dates along a single column of dates?

No problem, glad to help