3 Replies Latest reply on Jul 2, 2014 6:11 AM by Tim Buck

    Calculating 2nd to last sale date

    Peter Franzen

      I have 2 tables: customers and sales.  I'd like to create a calculated field for customers that shows their second-to-last sale date.  Can someone help me figure this one out?  It seems like I need to do a LOOKUP for the sale.date and then select the date via a "LAST()-1", but I'm having some trouble getting it to return anything.

       

      Thanks so much.

        • 1. Re: Calculating 2nd to last sale date
          Tracy Rodgers

          Hi Peter,

           

          Would it be possible to post the packaged workbook (twbx file) so that we can better help you?

           

          -Tracy

          • 2. Re: Re: Calculating 2nd to last sale date
            Peter Franzen

            Sure.  Here's a better explanation of what I'm trying to do:

             

            I want to count the number of people who have made transactions, then didn't make any for 18 months, and then made one within the current month.  We classify anyone who hasn't had a transaction in at least 18 months as "Inactive", and if they then make a new transaction we classify them as "Reactivated".  We want to count how many reactivations take place each month.

             

            The end goal is to have a chart to show how many people went from "Inactive" to "Reactivated" during each of the last 12 months.

             

            I have a field in the database that lists a person's most recent transaction date, but the crux is figuring out how much time elapsed between the second most recent transaction date and the most current one.  If that amount of time is at least 18 months then they were inactive, and if the most recent one was within this month they should be counted as reactivated.

             

            Thanks for the help.  I'm just on a demo of Tableau right now and I need to know if this calculation is possible, and I'm still wrapping my head around how to do this sort of thing within the program.

            • 3. Re: Calculating 2nd to last sale date
              Tim Buck

              Any luck with this?