0 votes
1 view
in BI by (16.3k 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 (43.4k 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.

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers !