Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Payment Schedule

cireost
8 - Asteroid

Hi - I have a payment schedule that is summarized annually that I would like to expand to show monthly.  Additionally, there are two columns that dictate the Start and End date of the lease.  The EFFDATE column shows when the rent amounts are effective.

 

Please see attached input and output.

 

I believe the tool to use is a multirow tool but having trouble with the expression.  Any sample workflows would be greatly appreciated.

 

Thanks!

6 REPLIES 6
danilang
19 - Altair
19 - Altair

Hi @cireost 

 

Here's a WF that accomplishes what you're looking for.

 

WF.png

 

It starts by using a multi-row to get the [next EffDate] from the next row, then generates the monthly rows.  The Filter and Select tools remove the months before the lease starts and clean up the results, respectively.

 

The output looks like this (showing the rows where the payment changes, but all rows are included)

 

Results.png 

Dan

cireost
8 - Asteroid

This is great! Couple tweaks -

 

1) if the Start date was instead 4/15, this would skip the month of April and move on to May, losing the ability to prorate the month of April.  Is there a way to address that? 

2) if there is another Property in the list, I think the expression in multi-row tool might need to be tweaked?  Not sure how though.

3) If the first effective date is not the first of the month, is there a way to update the loop expression in the generate row tool to return the first of the next month? 

 

Updated example attached.

 

Thanks!

danilang
19 - Altair
19 - Altair

Here you go

 

Incorporating the 3 "tweaks" you requested. 

 

WF prorated.png

Prorated.png

 

 

Dan

cireost
8 - Asteroid

this is awesome.  

 

for item 3 - looks like you added a formula (8) tool to change the EFFDATE to the first of next month.  what I'm actually looking for is not to change that date but the next generate row should be 5/1, 6/1 and so on.  please see updated input/ouput.  hopefuly this is the last!

 

Thanks!

danilang
19 - Altair
19 - Altair

Hi @cireost 

 

I've given you a good start with these 2 workflows.  Why don't you try making the changes you need from this point on.  If you run into specific issues, post your modified workflow and I'll try to help you out.

 

Dan 

cireost
8 - Asteroid

hi @danilang - please see attached excel worksheet with example input and output. Also attached is the workflow.  I cannot seem to get the pro-rations to work.  As a refresher, I am simply trying to expand a Rent Payment schedule to show monthly rent, however, issue come up when the start dates are in the middle of a month.  Thanks!

 

 

Labels