1 Reply Latest reply on Nov 14, 2018 5:40 AM by Yuri Fal

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


      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!




      #Weeks Active
      KeyWeek 0Week 1Week 2Week 3Week 4Week 5Week 6Week 7Week 8


      e.g. what I want at the end is