2 Replies Latest reply on Mar 18, 2013 1:22 AM by Donna Coles

    Calculate number of new records and number of lost records for two dates

    Donna Coles

      I have a set of data from which I can show the distinct set of customers who ordered something in 2011 and the distinct set who ordered something in 2012.  Some customers ordered in both years, some in only one of the years.  I can see the total difference in the customer base between the two years, but what I want to be able to show is the number of customers who are 'lost' (ordered in 2011 but not in 2012) and the number who are 'new' (ordered in 2012 but not 2011).

       

      I'm sure this is possible, but my table calculation skills are not yet up to that level.  Basic sample of data attached. 

       

      Many thanks in advance for any help.

        • 1. Re: Calculate number of new records and number of lost records for two dates
          Tracy Rodgers

          Hi Donna,

           

          By creating a calculated field similar to the following should get you the desired output:

           

          if window_sum(countd([Customer (copy)]))=2 then "Returned"

          elseif countd([Customer (copy)])=1 and max([Ordered Year])=2012 or (isnull(countd([Customer (copy)])) and max([Ordered Year])=2011) then "New"

          elseif countd([Customer (copy)])=1 and max([Ordered Year])=2011 or (isnull(countd([Customer (copy)])) and max([Ordered Year])=2012) then "Lost"

          end

           

          Place this on the filter shelf and Exclude Null.

           

          Hope this helps!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Calculate number of new records and number of lost records for two dates
            Donna Coles

            Thank you Tracy, that does indeed get me towards what I want.

             

            I've applied your suggestion and now have a 'customer status' type field against each customer row, but I can't seem to get a total for these.  My ultimate aim is to get figures for '#customers in 2011', ' #customers in 2012', '#new', '#lost''.

             

            I'm trying to do this within tableau as I'm ideally looking for a flexible solution that I could use if I changed years, or changed the 'cut' of the customers I'm looking at.

             

            I'm not worried if the totals for 2011/2012 can't be presented in the same view as the new/lost figures....

             

            Regards

             

            Donna