Circular referencing in columns

Question

Hello,

I have an issue of circular referencing of columns in Alteryx. I am trying to calculate the daily opening and closing stock but opening stock column is added first and it is linked to closing stock column. While working with a column in alteryx, we can work on same column or the columns added before but not on the columns to be added.

My calculation looks like this:

Date       Consumption     OpeningStock    ClosingStock
01-01-2018          8                        100                    92
02-01-2018          2                          92                    90
03-01-2018          6                          90                    84
04-01-2018          2                          84                    82
05-01-2018          0                          82                    82

Any help or input will be appreciated.

Thanks.

Sunit125 · Answer

Hi SeanAdams,

As I am new to this technology and I have also similar requirement what you are suggesting as a solution with Batch Macro.

Thank you for your valuable information.

If you can, could you please give some sort of sample WF with batch macro.

Thank You once again in advance.

SeanAdams · Answer

the trick here @mite40  is to use a multi-row formula or potentially a batch macro.

A multi-row formula is the tool that you use to calculate something on row 2 based on something in row 2 - it reads your data set from top to bottom - most of the other data prep tools operate on the entire data set at once.

In terms of macros - you could pump each all rows into a batch macro and use the control parameter to indicate the row you're working on - that way you can update multiple fields row by row (not just 1 field like in the Multi-row formula)

But for carrying-down the closing price to next-day's open price - that's a simple job for multi-row tool.

Qiu · Answer

@mite40 
So the table is the output?

How about the input?😁