KNOWLEDGE BASE

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


Published: 11 Nov 2021
Last Modified Date: 12 Nov 2021

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

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.
Did this article resolve the issue?