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.
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
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?
No. I think this doesn't work for me
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.