4 Replies Latest reply on Oct 27, 2017 1:44 PM by Justin Larson

    New, Lost or Existing Customers by Month

    Wes Reneau

      Hi Friends,

       

      I’m struggling to find a solution to figure out how many customers are existing, new or lost on a monthly level. I need to show the change of those categories (existing, new, lost) at a country level even though the data is at a customer id level.

      • Existing simply means the customer ID had a record in the data set for the prior month. The first month counts can be null.
      • New means the customer id does not have a record with a prior month date.
      • Lost means the customer id had a record last month, but not this month.

       

       

      With the help of Ivan Young, I tried using Fixed LOD’s but soon found out that fixed LODs filter before any dimension filters are applied, meaning it only worked for the latest month. That question and solution can be found here. https://community.tableau.com/thread/248110

      Unfortunately, since I need to show this at as an ongoing report that solution won’t work. I need all months to display at once, rather than using a context filter.

       

      I’m trying to use Tableau to avoid building an additional table/view, but will revert to staging the data using SAS if this is not possible. However, I figure it is and I just cannot figure out the calculations.

       

      I've attached a Tableau workbook with the same sample data i used to create the snapshot above.

       

      Thanks in advance.