Inventory Tracking

[Jack6627]

Hello.

I have an inventory tracking problem that I am having trouble with. I have been trying to use the multi-row formula tool but keep running into circular references. I have attached a sample csv and a notepad which explains how each formula works. I think this will require an iterative macro since it is a row-by-row calculation but I don't know how to even get started with one because of the circular reference issue. I will be happy to clarify any further doubts. 🙂

Thanks!

[dougperez]

Can you post some screenshots about this circular references? And attach your current workflow (if you can)?

[Jack6627]

Hi @dougperez  thank you for your response. Attached is a sample workflow of 2 items. If you observe, on row 2, my Closing Balance really should be 12, instead it says 0.

[chukleswk]

I'm trying to work through this and one of the issues that I see is that your calculation for your "Order Quantity" column is wrong. with an IIF statement you are only allowed the following: IIF( boolean_expression, true_value, false_value ).

If I understand it corrrectly,

• if [RecordID] = 1 then we will return 0
• if [PO Raised]="No" then we will return a 0
• Elseif [Total]-[Closing Balance])<0 then we will return 0
• Elseif [Total]-[Closing Balance])-[Row-1:In_Transit])<0 then we will return 0
• Else we return [Total]-[Closing Balance]))-[Row-1:In_Transit]

[Jack6627]

Hi @chukleswk . Thank you for your response. I had asked this question earlier in the community and one of the members confirmed it here that we can use nested ifs in alteryx. Or are you saying that the formula itself is incorrect?

Solved: Nested ifs in Multi-Row formula - Alteryx Community

One thing is noticed is that unlike excel, where after updating the formula, all the cells affected by the formula get populated, in alteryx that is not the case, it all depends on the sequence of the formula within the workflow. Is my understanding correct? Is that what could be causing the issue?

[dougperez]

In your second row, the calculation will do 0 (see the image below)

If you want some calculations like: if this row is 0 then take the value from row-1, you should do put this formula in your multi-row:

if ([Opening Balance]-[Invoiced]+[Received Quantity]) = 0 then [Row-1:Closing Balance] else
[Opening Balance]-[Invoiced]+[Received Quantity] endif

[Jack6627]

Thank you for your response @dougperez . I updated the formula for closing balance and it works for the first two rows but not from the 3rd row onwards. You will notice that for row 3, my closing balance should be 12-4+0 = 8, instead it calculates -4.

Thanks

[dougperez]

So the calculation is not:

[Opening Balance]-[Invoiced]+[Received Quantity]

Instead is:

([Closing Balance]-[Invoiced]+[Received Quantity]

[Jack6627]

Thank you for the quick response @Doug . The formula for [Closing Balance] will ALWAYS be [Opening Balance]-[Invoiced]+[Received Quantity].

The change occurs for the [Opening Balance] formula. For [Record ID] = 1, it will be [CS+SS] of the current row but from [Record ID] 2 up to [Record ID] 368 it will be the [Closing Balance] of the previous row.

Please let me know if this is not clear.

Thanks

[Christina_H]

Here's my attempt, borrowing the method from here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Best-way-to-calculate-row-by-row-as-ne...

I'm not sure I've got the calculations entirely correct, but you can check whether it gives the results you expect.