KNOWLEDGE BASE

How to aggregate the second and third purchase amounts based on the first purchase date


Published: 04 Oct 2022
Last Modified Date: 12 Oct 2022

Question

How to aggregate the second and third purchase amounts based on the first purchase date.
Fixed on each customer on their first purchase date, and calculate the total grand of the second and third purchase amounts.
For example:  A and B both buy on 2022/4,the date column 2022/4 will only count A and B, for the 2nd time to buy: A on 2022/5, B on 2022/5, so the total times are 2. the 3rd time of A is on 2022/7 and B does not buy the 3rd time, so the 3rd time is count 1 only, etc. 

User-added image

Environment

  • Tableau Desktop 

Answer

1. Create a calculated field and name it 1st purchase date:
{FIXED [customer],[category] : MIN([date])}
2. Create a calculated field and name it 2nd purchase date: 
{ FIXED [customer],[category] : MIN( IF [date] > [1st] THEN [date] END ) }
3. Create a calculated field and name it 3rd purchase date:
{ FIXED [customer],[category] : MIN( IF [date] > [2nd] THEN [date] END ) }
4. Create a calculated field and name it 1st purchase amounts: 
IF [date]=[1st] THEN 
{FIXED [customer],[category] : MIN( IF [1st] = [date] THEN [value] END ) }
END
5. Create a calculated field and name it 2nd purchase amounts: 
IF [date]=[1st] THEN
{FIXED [customer],[category] : MIN( IF [2nd] = [date] THEN [value] END ) }
END
6. Create a calculated field and name it 3rd purchase amounts: 
IF [date]=[1st] THEN
{FIXED [customer],[category] : MIN( IF [3rd] = [date] THEN [value] END ) }
END
7. drag [date] to row and measure name to column.
8. aggregate measures by count and sum.

see the attachment below.
Did this article resolve the issue?