Reduce Balance of Dynamic Columns

[markalves]

Hello,

I want to reduce the reserve we have for certain months.  But since the months always change this needs to be dynamic.  I want to make it so that if we get claims in April it will reduce the April reserve, then if April reserve is less than 0 it will reduce the reserve of the previous month and then if that month also has a zero balance then reduce from the next, and so on.... 

I have attached a sample workflow that I have so far.  I am stuck on the logic of creating the formula for reducing previous month's reserves if less than 0 since the column headers always change. We need to keep track of each month's reserve balance. 

[markalves]

Hello,

I want to reduce the reserve we have for certain months.  But since the months always change this needs to be dynamic.  I want to make it so that if we get claims in April it will reduce the April reserve, then if April reserve is less than 0 it will reduce the reserve of the previous month and then if that month also has a zero balance then reduce from the next, and so on.... 

I have attached a sample workflow that I have so far.  I am stuck on the logic of creating the formula for reducing previous month's reserves if less than 0 since the column headers always change. We need to keep track of each month's reserve balance. 

[ArtApa]

Hi @markalves - This sounds like a use case for an Iterative Macro:

 - https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Macros

 - https://community.alteryx.com/t5/Videos/Build-Your-First-Iterative-Macro/td-p/55565 

[ddiesel · Accepted solution]

Hi @markalves!

I set out to build a interative macro to help you. When I looked closer at your sample data, I saw an easier solution!

Let's give this a try:

The provided sample dataset was very small, but this should be a dynamic solution no matter how many parts # and months you have. Will you hook this up to your real data and let us know if it does the trick? 

Thanks,
Deb

[markalves]

This worked perfectly. Thank you so much. 

[ddiesel]

@markalves 

You're welcome! Glad it helped!

[markalves]

It turns out that the workflow does not work if there are more than 2 months of reserve.  If there is 3 months then it adds more reserve than I originally had. I have uploaded your workflow with the addition of another month of reserve. How can we solve this?  100+100+100-120 should = 180 but in the workflow it equals 260.