Explore Courses Blog Tutorials Interview Questions
0 votes
in BI by (17.6k points)

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?

1 Answer

0 votes
by (47.2k points)

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. 

Related questions

Browse Categories