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. 

The workflow I attached was given to me months ago by someone in the community, and I thought it was solved, but when I added a 3rd month the math did not work. It only works for 2 months of reserve. 100+100+100-120 should equal 180, but in this workflow it equals 260. 

[Kinga]

hi @markalves 

I've added to your workflow two tools:

I hope I correctly understood, what you need

[markalves]

This looks good (logic is sound after playing with the numbers), but I was hoping to reduce the reserve of the older month's reserve first before reducing the current month. I thought maybe sorting by ascending would fix but it doesn't. Any thoughts?

[markalves]

Hi Kinga,

Just wanted to follow up on this.  Wondering if you had a solution?

[Kinga · Accepted solution]

Hi markalves,

I made something like this.

[markalves]

I had to throw in a data cleanse at bottom after cross tab, then it worked. I'll accept as solution though.  Thank you so much!