community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Payment Schedule

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!

Pulsar

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

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!

Pulsar

Here you go

 

Incorporating the 3 "tweaks" you requested. 

 

WF prorated.png

Prorated.png

 

 

Dan

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!

Pulsar

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 

Labels