I'm trying to get our clients' life cycles into one table that shows all of their terms. I have the following data points:
Initial Term Months
Renewal Term Months
Initial Term = Start Date + Initial Term Months + If (Extension Date < (Start Date + Initial Term Months), Extension Months, 0)
Renewal Terms = Last Term End + Renewal Term Months + If (Extension Date > Last Term End AND Extension Date < Last Term End + Renewal Term Months, Extension Months, 0)
The logic is very simple but I'm having a problem pulling in the prior calculated values. My R.Initial Table already has the initial term calculated. The logic is that I want the script to continue to run until the client's Current Term's end date is greater than today and then move onto the next client.
Everything I have works except for getting the variables to update with each successive row. Once I solve that everything else will work.
NoConcatenate Load *,
IterNo() as R.TermNumber,
If(IterNo() = 1, R.InitialTermEnd,AddMonths($(vPriorTermEnd),R.RenTerm...) as R.TermEnd,
RowNo() as R.RowNumber,
Set $(vPriorTermEnd) = If(IterNo() = 1, R.InitialTermEnd,AddMonths($(vPriorTermEnd),R.RenTerm...), //Same formula as R.TermEnd
Example A is the simple example. Example B uses a 12 month extension that occurred in term 3(notice a 3 year term instead of 2). Example A is simple and can be figured out using just a formula, but the only way I can figure out Example B is by using a loop and checking for the extension during every term.