1 Reply Latest reply on Oct 26, 2018 12:22 PM by Yuriy Fal

    How to fix all the lookup values and aggregate - customer reactivation & attrition

    jieqiong.xu.0

      Hi, this is a sample of my data. The "key" represents customer id. The 0/1 means if the customer is active or not (1 is active and 0 is inactive, null means there is no value for the customer).

       

      What I want to calculate is that on individual customer level, if the previous week is 0/1 and the week after is the same 0/1, it means "no change"; if previous week is 0 and week after is 1, it means "reactivation"; if previous week is 1 and week after is 0, it's "attrition".

       

      I can easily use lookup to find those value but what I want is an aggregate value, meaning if I take all the key out, I want an added up value of how many "reactivations" per week and how many "attritions" per week. I tried to use fixed but no success as fixed and lookup cannot use together. Please help if you could come up with some solutions. Thanks!

       

      Sample:

       

      #Weeks Active
      KeyWeek 0Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8
      3|9519125111111011
      3|94329703101100110
      3|95798942111
      3|98529592101101001
      3|94193229211000000
      3|942010003101101111

       

      e.g. what I want at the end is

       

      Reactivation003002111
      Attrition031030101