How do I get the Average number of days between multiple dates in Tableau

Question

I'm using Tableau and I have a field called Sales_Order_Date. I need to get the AVERAGE number of days between each sales order.

Example: I have 3 orders

1. 01/01/2014
2. 20/01/2014
3. 30/01/2014

The number of days between order 1 and order 2 = 19 days The number of days between order 2 and order 3 = 10 days

The average is 29/2 = 14.5 days

How can I do this using a calculated field in Tableau for 000's of dates?

Thanks, Stefan.

Answer 1

window_avg(datediff('day',
                    lookup(min(Sales_Order_Date), -1),
                    min(Sales_Order_Date)))

assuming your calculated field is set to "compute using" the discrete dimension DAY(Sales_Order_Date)

Starting at the innermost sections and working out:

• the lookup() function calculates the date of the previous entry in the table
• the datediff() function computes the difference between the previous date and the date in the current row in days
• the window_avg() computes the average of all the intervals between recorded dates

In this example, I threw all the logic together into a single table calculation. When developing table calcs, its usually a good idea to build them up incrementally as a series of calculations that call other (lower level) calculations, and to test them using a table that shows all the intermediate steps. And only hide the intermediate steps and switch to a more visual representation after you are confident in the results.

An earlier version of this anwser also used an iif() call to avoid calling lookup() for the first row since there is no previous row in that case. That is redundant because most Tableau functions gracefully handle null data by ignoring it, which is exactly what makes sense here.