3 Replies Latest reply on Sep 30, 2016 2:15 AM by Sumesh Kewlani

    Deeper Dives on Lapsed Customers

    Rebecca Lane

      I am trying to create a new,lapsed, retained report for our business.  In using Superstore Sample terms it would give a status to each customer for each year.  Assuming everyone who bought in 2011 is 'NEW' then if i bought in 2012 i would be 'RETAINED' - if not i would be 'LAPSED'.  I've been able to assign values for each customer for each year utilizing a calculated field as follows:



      This allows me to create a chart as follows that shows the number of customers that fall into each account status category:


      I was able to have the 'Lapsed' customers show up as a negative number by creating the calculated field (I really wanted to show those are customers who did not add to the 'positive' side for that year):

      Now here's where it gets sticky.  I want to create a map that shows where these 'Lapsed' folks are from.  When i try to create a map it is only showing as -1 account in the different states:




      I also want to be able to show in total what each column (positive or negative) attributed in total sales.  Since the calculations are based off of individual customers when i add [Sales] to the view it only shows the sales for that specific account that i'm hovered over - not the sales of the total column.


      Sample workbook is attached.  Relatively new to Tableau so if this is a beginner-level question I apologize!

      Thanks in advance for any questions, comments, suggestions!

        • 1. Re: Deeper Dives on Lapsed Customers
          JT Louviere

          Hey Rebecca,


          My quick recommendation is to (in regards to your map) move account status to the color mark in the marks shelf. That will visually represent the difference between lapsed and lapsed 2 years.


          As for your column question, I'm not exactly sure what you are trying to accomplish. If you want to visually represent a total sum of the sales for each year you will need to create a separate calculation. After you create that calculation, you can add that to the tooltip mark in the marks card. I hope this helps, thanks!

          • 2. Re: Deeper Dives on Lapsed Customers
            Lauren Bearden

            Hi Rebecca,


            Totally agree with JT above on the first part! I've attached a workbook showing how you can do this more specifically. The worksheet "Account status on color" shows the colored view with all of the account statuses. To only show Lapsed and Lapsed 2 years, put account status on filters, and only show the chosen statuses (this is shown on worksheet "Filter to account status").


            If I understand correctly, for "Sheet 2", you want to show sum(Sales) for the bars without getting the bars broken up by the different Customer IDs... is that correct? The reason this is happening is because you must have Customer ID on the level of detail since you are using a table calculation, "PREVIOUS VALUE" in your "Account Status" calculation. I've tried a few alternatives to previous_value, but it does seem using previous value is your best option.


            My recommendation is to create a new calculations, almost identical to "# OF CUSTOMERS", but substituting sum(Sales) for each of the count distinct of customers. This will allow you to use the Sales numbers in the same way you use the customer count. In the attached workbook, this calculation is called "# OF SALES" and shown on the worksheet "Number of Sales". You can then add a reference line to each cell to show the total of these sales.


            Hope that helps!

            • 3. Re: Deeper Dives on Lapsed Customers
              Sumesh Kewlani

              Hi Rebeca,


              I used similar idea of defining Customer / Account Status. Graph works fine.

              However, there are 2 issues -

              1. I am not able to count # of customers with each of the status

              2. I loose history of the Account Status based on selected period.. for eg. if a customer was "New" in December'2012 and "Retained" in January'13, status of the customer will change  to "New" if you selected period of January'13 as a starting period.

              Not sure if you encountered same problem and how did you resolve this?