Weighted Average Cost computation in Alteryx

[Buddhi_DB]

Hi Community,

I've built a WAC inventory costing workflow using alteryx.

1st Multi Row Formula

I want to compute first running value . But part of expression is linked with 3rd Multi Row Formula. "[Row-1:Average Cost]" part in the below expression is coming from the 3rd Multi Row Formula.

Expression : 

IF [GL Entry No] = 1
THEN [QTY transfer cost]
ELSEIF [Source Code]="SALES"
THEN ([Row-1:Running Value] + ([QTY transfer] *[Row-1:Average Cost]))
ELSE [Row-1:Running Value] + [QTY transfer cost]
ENDIF

2nd Multi Row Formula

[Row-1:Running QTY] + [QTY transfer]

3rd Multi Row Formula

IF [GL Entry No] = 1
THEN [Cost per unit]
ELSEIF [Source Code]="PURCHASES"
THEN [Running Value] / [Running QTY]
ELSE [Row-1:Average Cost]
ENDIF

Issue

The expected result from the first multi-row formula is not coming as I believe the reason is the interdependence of the first multi-row formula tool with the third multi-row formula in my Alteryx workflow.

sample calculation.xlsx shows the manual calculation and expected result from the Alteryx. Please anyone help me to solve this problem.

[Qiu · Accepted solution]

@Buddhi_DB 
As we can see the column "Running QTY" are somehow "independent", meaning it only has relation with its previous row and the data from input.
while for "Running Value" and "Average Cost" are somehow tangled together.
Since the Multi-row tool will update the data all in once, I dont think the Multi-Row tool can be applied for the calculation of "Running Value" and "Average Cost".

Good thing is that we have the iterative macro for this case.

[Buddhi_DB]

Thank you very much

[Buddhi_DB]

Hi @Qiu , Using the iterative macro wokflow you created, I tried to run the computation for the full inventory movement report.  Do you know the reason for this limitation and long processing time.

Actually I'm quite know for the Alteryx and still learning.

[Qiu]

@Buddhi_DB 
IT seems you have really a large data. RIght click on the Macro icon and Choose the Open Macro on the bottom.

Then open the Interface Design windown, increase the Number of interations to be larger than your row of data.

Actually, this weekly challenge is really the same question with yours. 😁

https://community.alteryx.com/t5/Weekly-Challenges/Challenge-431-Ice-Cream-Road-Trip-Summer-Edition/...