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?

Answer 1

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

• Lets assume that 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 used all the logic together into a single table calculation. When developing table calculations, 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.

If you want to learn more about tableau analytics then you can check our Tableau Desktop Specialist Certification Course.