
1. Re: How to fix all the lookup values and aggregate  customer attrition & reactivation over weeks
Michel Caissie Oct 8, 2018 12:37 PM (in response to jieqiong.xu.0)1 of 1 people found this helpfulJieqiong,
Check in the attached...
On sheet validateData1 I compute the previous status with
lookup(ATTR([Status]),1)
compute using key,month,status restarting every key
Next I compute the changes with
case [previousStatus]
when 0 then
case ATTR( [Status] )
when 0 then 'no change'
when 1 then 'reactivation'
end
when 1 then
case ATTR( [Status] )
when 0 then 'attrition'
when 1 then 'no change'
end
end
Next if we go on the sheet validateData2, I swapped the key and month dimension so it is easier to view the per month changes quantities.
Here I compute the number of attritions and reactivations with
WINDOW_SUM( if [changes] = 'attrition' then 1 else 0 end )
pay attention to the computing, it is now month,key,status restarting every month
But since it is a nested calculation, you also have to set the computing of previousStatus, which have a different computing, with the dimensions in a different order. (in the compute using  Specific Dimensions pane, you can drag the dimensions up/down to get the right order)
Now we have the monthly values on every row. So I compute a boolean calculation returning true on the first row
first() = 0
compute using month,key,status restarting every month
On the finalView sheet, you need to have Key and Status on the details shelf, and the Keep a single row calculation on the filter shelf , keeping the True values.
Make sure all nested calculations have the right computing with the good dimensions order.
Michel

2. Re: How to fix all the lookup values and aggregate  customer attrition & reactivation over weeks
jieqiong.xu.0 Oct 9, 2018 6:12 AM (in response to Michel Caissie)This is super helpful and exactly what I was looking for. Thank you so so much!