Hi,
I was just dealing with a similar case. What exactly is it you need to calculate / what is the methodology ?
Try this script to calculate Retention amount for year 1 and 2. it may help.
OriginalData:
LOAD * INLINE [
Year, Cash Donor, Amount
2009, 1, 302
2009, 1, 300
2010, 1, 353
2011, 1, 350
2012, 1, 358
2010, 2, 304
2012, 2, 305
2013, 2, 11
2016, 2, 30
2017, 2, 15
2017, 2, 20
2013, 3 ,45
2014,3,55
2015,3,65
];
//Calculate retention:
Aggr:
Noconcatenate Load
*,
[# Years Donated] / [# Years Total] as [Years Retention %]
;
Load
[Cash Donor],
Min(Year) as FirstYearDonation,
Count(Distinct Year) as [# Years Donated],
Year(Today()) - Min(Year) as [# Years Total]
Resident OriginalData
Group by [Cash Donor];
Map:
Mapping Load
[Cash Donor],
FirstYearDonation
Resident Aggr;
//1Y Retention:
[Retention]:
Noconcatenate Load
*,
If([@1Y Retention]=1,'1Y Retention',
If([@2Y Retention]=1,'2Y Retention', Null())) as Retention //to be used as a Dimension
;
Load
Year,
[Cash Donor],
If(Year=FirstYearDonation+1,1,0) as [@1Y Retention], //Flag to be used in set analysis
If(Year=FirstYearDonation+2,1,0) as [@2Y Retention],
If(Year=FirstYearDonation+3,1,0) as [@3Y Retention]
;
Load
[Cash Donor],
Year,
ApplyMap('Map',[Cash Donor],Null()) as FirstYearDonation,
Sum(Amount) as AmountAggr
Resident OriginalData
Group by [Cash Donor],Year
Order by [Cash Donor],Year;
Left Join(OriginalData) Load * Resident Retention;
Drop table [Retention];
