2 Replies Latest reply on Jun 10, 2016 4:05 PM by Ivan Young

    Average No: of Days Between Transactions

    stephen zacharias

      I have sales transaction dataset that shows the date of transaction, name of the buyer and the price he/she has paid for the item.  My aim here is to identify the number of days between each of the transactions. The catch here is that, the formula should not calculate the transactions that happend on the dame day.

       

      For instance, if my transactions lines are like the following

       

      03/01/2016   Paul Smith  $55

      03/01/2016   Paul Smith  $67

      08/01/2016   Paul Smith  $26

       

      i want tableau to return the avg: no of days between transaction to be 5 (8-3), not 2.5. So essentially the same dates should be seen as a distinct value by tableau.

       

      Thanks;

        • 1. Re: Average No: of Days Between Transactions
          Tina Hauser

          Hi Stephen!

          Please see the attached workbook. Are you calculating days between transactions for a specific buyer, or just overall? You may need to use a level of detail calculation depending on the exact requirement.

           

          But, since your data was for the same buyer I went ahead and put that data into Tableau. This will illustrate how you can accomplish this.

           

          There are 2 calculations:

          Previous date (this will allow you to use the DATEDIFF function to compare 2 dates): LOOKUP(ATTR([Date]),-1)

          # Days between transactions: DATEDIFF('day',[Previous date],ATTR([Date]))

          • 2. Re: Average No: of Days Between Transactions
            Ivan Young

            Hi Stephan and Tina,

            Check out the method in this post Average of days from last transaction  for determining avg days between transactions, it would need to be modified to use a countd(date) as the denominator.  Using lookup can work to get the average but it will require you to have the date field in your view.  Using the method in the post provides a bit more flexibility on how you can use the average.

             

            Regards,

            Ivan