5 Replies Latest reply on May 6, 2019 3:36 AM by Nagendra Ganti

    Trying to calculate Active status (New, Retain and Churn) for a customer using start date and end date.

    Nagendra Ganti

      Hi All,

      Me and my team is trying to calculate Active status (New, Retain and Churn) for a customer using start date and end date.  I have attached  sample workbook from earlier post in the forum which I was using for testing.

      I have tried to explain in detail my request below. I would really appreciate if you guys could help me find a solution.

      In below image we were able to solve stage 1 where we wanted to check the active status for a customer in the give quarter  using below solution from the forum.

      Identification of Active Customers in a Given Period

      Stage 2 and 3 focuses on converting the Stage 1 into Active customer status.  So we are trying to create a Stacked bar which will show customers who are new , Retained or Churned in specified quarter.

      My Approach so far for stage 2 and 3 :

      I have tried to create Window_Count() function and  used if else statement  to assign the status for each customer and Quarter. However it doesn't help me in achieving Stage 3 which I want to group Status and Quarter and remove Customers.

      Calculations I created:

      Active Status:

      if [Window Calc]= 1

      then "New"

      elseif [Window Calc]= ((-1*sum([Quarter diff]))+1) then "Churn"

      elseif [Window Calc]>1 and [Window Calc]< ((-1*sum([Quarter diff]))+1) then

      "Retain"

      end

      Window Calc:

      WINDOW_COUNT([CountD Customer],sum([Quarter diff]),0 )

       

      Message was edited by: Nagendra Ganti