Create dummy records for contract renewals through a specific date

[MellowMashMellow]

Hi there, 

I'm trying to create dummy renewal records for the existing contracts through 12/31/2026, assuming the current contract value will increase by 3% and then stay at 3%. Renewal contracts will have the same contractual term length as the original contracts. The difficulties are:

1. Contracts can be renewed by month or day or year and I couldn't find an automated way to identify the pattern.

2. How to make only the first renewal's contract value to increase by 3% and the remaining renewals stay at the same?

The excel upper part is the original data, and the bottom part "updated" is my expected result. I have a workflow (see attached) but it's not generating the expected output. 

Any ideas where the workflow is wrong? Or how can it be fixed to generate the desired output? 

Thanks!

[CoG · Accepted solution]

Here is a solution to your conundrum. I cleaned up and optimized the first half of your workflow, which nearly accomplished what you were looking for. The only thing missing was that you stopped the Generate Rows Tool on [End Date] instead of [Start Date].

I also added 2 versions for ways to calculate the 3% increase:

Happy Solving!

[Carolyn · Accepted solution]

Give this a try. 

What I did:

1. Instead of calculating 3 different "Term Length" fields, I used the value in the "Type" field in the formula 
2. I tweaked your Generate Rows to again use the "Type" field
3. I added a Multi Row tool to leave the TCV alone for the original line and then multiply all the subsequent lines by 1.03 to indicate the increase. I'm assuming that if Renewal 1 is increased by 1.03, then Renewal 2 would equal Renewal 1, vs going down to the Original value. If that's wrong, let me know and I can help you tweak 

    

Let me know if this works for what you're trying to do! If it does solve your issue, please mark the topic as resolved. 

[Carolyn]

@CoG - Not OP, but your way of doing the Generate Rows is very pretty :) 

[Qiu]

@CoG @Carolyn 
Nice solution.

I was actually working the first part to come up with a logic to identy the renewal period is Day, Month, Year.
It appears the OP manually flagged the type as below.

[Carolyn]

@Qiu - Yeah, I was trying to figure out how to do that from the Excel data, especially in the cases where it was 2 years or 3 months or whatnot. Then I saw the Text Input of the provided workflow and breathed a sigh of relief :) 

My initial thought for figuring out the Day/Month/Year was using a DateTimeDiff and MOD calculation, but that's as far as I got.

[MellowMashMellow]

This is awesome and solved the problem. Thanks! One follow-up: while I tag those renewal type as Month or Year or Days manually, in reality I'm dealing with a much larger dataset and wondering if there is a way in Alteryx to tag that automatically?

[MellowMashMellow]

That's actually part of the puzzle I was trying to solve. The dataset was much larger and I was only able to tag the renewal type manually in Excel. Was curious if there is a logic that can be built for it. 

[CoG]

Here is one way to do the calculation, but I must caution you, this is not a well-defined procedure. The yearly check will fail for leap year contracts where start date = February 29, and the monthly check will fail for the start date = last day of some months. I included examples of failed cases in the sample data under [#]>=9990 (notice the difference between [Type] and [Calculate_Type]. This may not be an issue for you, but it's important that you double check any "Day" types that meet failure criteria, so that you are not unknowingly introducing errors into your data.