Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.

Transferring Balances from Prior Months

Highlighted
5 - Atom

Hi Community, 

 

I have constructed a workflow to perform the cross calculations shown in the image below (opening balance + additions - subtractions = ending). What I want to happen, as shown in the image, is for the workflow to run the "+ additions - subtractions" calculations for Feb using "ending balance" of Jan as the "opening balance" of Feb. My data is stacked as shown within the image (the data is sorted by individual ID and month so that all months (Jan - Dec) are in order for Person 1 and then all months (Jan - Dec) are in order for person 2) and my preference is to not need 12 separate branches within the workflow to separate out each month of the year.

 

The two issues I am running into is that 1) the workflow is not recalculating Feb "Opening Balance" once Jan "Ending balance" is updated and 2) Feb "Ending Balance" is not recalculating even if it's "Opening Balance" had values.

 

I am looking to see if there is a tool, or combination of tools, which would allow me to tell the workflow to first calculate all rows marked as Jan for all individuals. Then, once Jan is completed, commence calculations solely on the Feb rows for each individual. And so on until all months are calculated for all individuals.

 

alight005_0-1573588832552.png

Highlighted
Alteryx
Alteryx

@alight005 

 

I'd go for a combination of the Multi-row Formula followed by the Formula tool

 

The Multi-row Formula will allow you to calculate the Ending balance

 

IF !IsNull([Opening balance]) THEN [Opening balance]+[Additions]-[Subtractions] 
ELSE [Row-1:Ending balance]+[Additions]-[Subtractions]
ENDIF

 

multi-row.png

 

 

 

 

 

 

 

 

 

 

 

 

Once you have these in, you can use the formula tool to calculate the Opening balance

 

[Ending balance] + [Subtractions] - [Additions]

 

Example attached

Labels