Computing Cumulative Sum with Changes Statuses over time
I’m trying to compute a cumulative sum over week in a bar chart for a data set of customers . This works well when customers are active or pending deactivation (which means technically, they are still active). However, when they are deactivated it’s now challenging to track the accumulation. Please see an example of my problem below:
Id
Customer
Status
Status Date
a
J. Marshall
Active
9/4/2022
abc
M. Josh
Active
9/4/2022
abcd
R. Tom
Active
9/11/2022
a
J. Marshall
Deactivated
9/25/2022
cde
V. Jacobs
Active
9/25/2022
abcd
R. Tom
Pending_Deactivation
9/25/2022
abcd
R. Tom
Deactivated
10/2/2022
=RangeSum(Above(count(distinct(if([Status] = 'ACTIVE' or [Status] = 'PENDING_DEACTIVATION', id)),0,RowNo())))
The above code returns cumulative sums for each week:
9/4/2022: 2
9/11/2022: 3
9/18/2022: 3
9/25/2022: 4 (should be 3 since J. Marshall dropped off)
10/2/2022: 4 (should be 2 since R. Tom also dropped off)
I need help integrating the ‘deactivation’ status without affecting the historical statuses in the accumulation.
