How to calculate the average length between dates for each item.

Published: 11 Nov 2021
Last Modified Date: 28 Apr 2023

Question

How to calculate the average length between dates for each item.

User-added image

Environment

Tableau Desktop

Answer

How to find the date interval average for each part
1.Drag and drop the dimension [Parts] to the Row Shelf.
2.Right-click on the dimension [Date] and drag and drop it to the Row Shelf.
    Select "MDY (Date)"
3.Create Calculated Field to find the date interval.
     Name of Calculation Field : Date Interval
   Calculations
             DATEDIFF('day', LOOKUP(MIN([Date]),-1), MIN([Date]))
4.Create a Calculated Field to find the date interval average and enter the following calculation.
Name of Calculation Field : Average of Date Interval
Calculations
         WINDOW_AVG([Date Interval])
5.Drag and drop [Average of Date Interval] to the view.
6.In the Mark Pane, in [Average of Date Interval], in Edit Table Calculation, make the following settings.

Option 1

Nested Calculations : Date Interval
Computing Using : Specific Dimensions
Restarting every : Parts

Option 2

Nested Calculations : Average of Date Interval
Computing Using : Specific Dimensions
Restarting every : Parts
User-added image

How to combine each parts into a single line
7.Create a calculated field to set the [index].
Name of Calculation Field : Index
Calculations : INDEX()
8.Convert Index to discrete, drag and drop to Row Shelf.
9.In [Average of Date Interval], in Edit Table Calculation, make the following settings.
Computing Using : Specific Dimensions
Restarting every : Parts
10.Drag and drop [Index] to the Filter Pane
Select "1" in the Filter [Index] tab.
11.Click [MDY (Date)] on the row shelf and click Show Header.