How to Calculate the Number of Days between the Previous Purchase Date for Each Customer, And to Display the Average Number of Days for Each Purchase Frequency

Published: 03 Aug 2023
Last Modified Date: 07 Aug 2023

Question

How to calculate the number of days between the previous purchase date for each customer, and to display the average number of days for each purchase frequency.

Environment

Tableau Desktop

Answer

The following is an example of calculating the number of days between the 1st, 2nd and 3rd purchase dates for each customer, and displaying the average number of days for each purchase frequency.

User-added image

1. Create a calculated field and name it 1st Purchase Date.

{ FIXED [Customer Name] : MIN([Order Date]) }

2. Create a calculated field and name it 2nd Purchase Date.

{ FIXED [Customer Name] : MIN(IF [Order Date]>[1st Purchase Date] THEN [Order Date] END) }

3. Create a calculated field and name it 3rd Purchase Date.

{ FIXED [Customer Name] : MIN(IF [Order Date]>[2nd Purchase Date] THEN [Order Date] END) }

4. Create a calculated field and name it # of Days between 1st and 2nd Purchase.

DATEDIFF('day',[1st Purchase Date],[2nd Purchase Date])

5. Create a calculated field and name it # of Days between 2nd and 3rd Purchase.

DATEDIFF('day',[2nd Purchase Date],[3rd Purchase Date])

6. Add Measure Name to filter shelf and select # of Days between 1st and 2nd Purchase and # of Days between 2nd and 3rd Purchase.
7. Add Measure Name to Rows.
8. Add Measure Values to Columns.
9. Add Measure Values to Label.
10. Right-click # of Days between 1st and 2nd Purchase in Measure Values shelf and select Measure > Average.
11. Right-click # of Days between 2nd and 3rd Purchase in Measure Values shelf and select Measure > Average.