1 Reply Latest reply on Aug 25, 2017 9:07 AM by Tom W

    Calculation for Lost customers.

    shwetha sarma

      Hello,

       

      I'm working on a dashboard where I show the trend of lost and gained customers.

       

      I'm working on the formula for lost. I want to consider any customer lost, if they have not bought in the last 2 years or if they have not bought anything for 3 continuous months be it at any time period.

       

      I'm attaching a dashboard with Sample Superstore data. Please consider order date and ship date as First order placed and Last order placed respectively.

       

      Note: If this is not the correct data source for this, please help me with some ideas to write the formula. I'm unable to attach the actual data that I'm working with.

       

      Thanks,

      Shwetha.

        • 1. Re: Calculation for Lost customers.
          Tom W

          For the last 2 years piece;

          • Figure out the maximum date per customer. Perhaps a level of detail calculation like {FIXED [Customer]: MAX([Order Date])}
          • Once you have their maximum order date, you should check it to make sure it's within the last two years by comparing it to maximum order date using the DATEDIFF function.

           

          I'm not entirely sure on the three continuous month piece right now, hopefully this helps you get started on part of your problem.