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!
e.g. what I want at the end is