Reduce Balance of Dynamic Columns

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.

Claim Reserve Reduction.yxmd

Dynamic Processing

Accepted answers

ddiesel

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:

Capture (1).JPG

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

Claim Reserve Reduction_dmd.yxmd

All comments

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

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:

Capture (1).JPG

Thanks,
Deb

Claim Reserve Reduction_dmd.yxmd

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.

Claim Reserve Reduction (Edited).yxmd

Quick Links

This months top contributors

FláviaB 262

JORGE4900 235

caltang 177

DanM 167

IraWatt 157