CALCULATE THE NEXT RESULT USING THE PREVIOUS ONE - WITH TRICK

[Augusto]

Hi,

Please someone can solve this issue ?

Its need to calculate the next result using the previous one.

HAVE

segment	month	safra	qt_account_initial	attrition
varejo	30/06/2019	1	100	2,00%
varejo	31/07/2019	2	100	2,00%
varejo	31/08/2019	3	100	2,00%
affluent	30/06/2019	1	80	0,00%
affluent	31/07/2019	2	80	0,00%
affluent	31/08/2019	3	80	0,00%

WANT

segment	month	safra	qt_account_initial	attrition	new_qt_account	acc_whithout_attrition	attrition_to_new_segment
varejo	30/06/2019	1	100	2,00%	98,00	98,00	2,00
varejo	31/07/2019	2	100	2,00%	96,04	96,04	3,96
varejo	31/08/2019	3	100	2,00%	94,12	94,12	5,88
affluent	30/06/2019	1	80	0,00%	82,00	82,00	0,00
affluent	31/07/2019	2	80	0,00%	85,96	82,00	0,00
affluent	31/08/2019	3	80	0,00%	91,84	82,00	0,00

The TRICK is : THE attrition_to_new_segment result needs to be added to the AFFLUENT Segment line before the its calculation.

Please, use SAFRA EQ 1 for the condition to the first iteration.

I could get the calculation using the retain, but could not solve the trick.

[hashman]

@Augusto :

The calculation pattern for the 2 segments you've shown, "varejo" and "affluent" is clear. What is not clear is what should happen to the segment groups that follow. For example: Is the 3-record "affluent" segment followed by another 3-record segment other than "affluent"; and is the latter followed by a 3-record "affluent" segment again? Please show two more segments with their figures after the "affluent" segment you've already shown. Without it, it's impossible to infer the logic for the entire program.

Kind regards

Paul D.