To replicate Weighted Average Cost computation in Alteryx

[Buddhi_DB]

I've done a sample WAC computation using excel.

A:N columns are from movement report.

Key field
Column G - Trans Qty (That's the during the year QTY movement)
Column B- Type ( Sales order/Purchases order/Revaluations,etc)
Column I - Transaction Cost ( QTy movement related cost)
Column L - Average cost

G3 cell has the opening QTY as per last year valuation report
L3 cell has the opening unit WAC average cost as per last year valuation report.

R:T has my compuation.

Key columns
R-Cumulative value
S-Cumulative QTY
T-WAC average cost

There first line ,is opening value and QTY. R3 cell is =G3*L3 and S3 is =G3 and T is =L3

from row 4 to towards to end. R formula is "=IF(B4="SO",R3+(G4*T3),R3+I4)" and S is "=S3+G4" and T is "=IF(OR(B4="PO",B4="RV"),R4/S4,T3)"

How can I do my computation like this in alteryx

I tried to do this using Multi-Row Formula Tool. But I was not success. 

[Yoshiro_Fujimori]

Hi @Buddhi_DB ,

Yes, Multi-Row Formula is the way to go.

Here is a sample workflow.

I hope it helps.

Workflow

Expressions 

Running Value

IF [RecordID] = 1
THEN [Running Value]
ELSEIF [Type] = "SO"
THEN [Row-1:Running Value] + [Trans Qty] * [Row-1:Average Cost]
ELSE [Row-1:Running Value] + [Transaction Cost]
ENDIF

Running QTY

[Row-1:Running QTY] + [Trans Qty]

Average Cost

IF [RecordID] = 1
THEN [Trans Whse Avg Cost]
ELSEIF [Type] IN ("PO", "RV")
THEN [Running Value] / [Running QTY]
ELSE [Row-1:Average Cost]
ENDIF

[Buddhi_DB]

Hi @Yoshiro_Fujimori

Thank you very much for your effort. However, there seems to be a misunderstanding.

What I need is to recompute the average cost and compare it with the "Trans Whse Avg Cost."

I manually computed the Running Value and Running Quantity to get the Average Cost. I need to compute the Running Value, Running Quantity, and then the Average Cost in a similar manner and compare it with the "Trans Whse Avg Cost."

I don't want to use manual calculations for the Alteryx workflow. I want to upload the Stock Movement Report and Opening Valuation Report and compute it automatically.

Here's the workflow I built. I computed this using a multi-row formula. The final Running Value should be 2448, but I'm getting a different result. The Average Cost is also incorrect.

Can you help me with the multi-row formula?

[Buddhi_DB]

This is workflow I'm trying to build

[aatalai]

@Buddhi_DB have you tried using the weighted average tool?

[Buddhi_DB]

No. I think this doesn't work for me

[Buddhi_DB]

I think the issue is  interdependent multi-row formula tools in my Alteryx workflow. Interdependencies where the calculations from Step 2 (Running Value) and Step 3 (Average Cost) depend on each other.