Multi-row calculation for fields that are inter-dependent

Question

Hi everyone,

I have a problem with calculation in Alteryx and I will really appreciate if you give me some guidance. My case is as follow (please kindly refer to the attached photo for an illustration):

* Data in column A, B and C of all records are given
* Data in column D, E and F of the 1st record are given
* I need to calculate values column D, E and F of the remaining records.
* Column D of the current record = the sum of Column A, D and F of the previous record
* Column E would be the difference between Column B and Column D
* Column F is equal to either column C or E, whichever is lower

Is there any way for me to calculate values column D, E and F for a large number of records in an efficient way? I have tried to find solutions for similar cases, but there does not seem to be any.

Thank you very much for your help.

flying008 · Answer

Hi, @nghieu

Here you are :

0- Change all Calculation Field type to Double.

1- Calculate the Column D.

IIF([ID]=1,[Col_D], [Row-1:Col_A]+[Row-1:Col_D]+ min([Row-1:Col_C] ,[Row-1:Col_E]))

or change to the formula:

Spoiler

IIF([ID]=1,[Col_D], [Row-1:Col_A]+[Row-1:Col_D]+ min([Row-1:Col_C] ,[Row-1:Col_B]-[Row-1:Col_D]))

2- Calculate the Column E and F.

IIF(IsEmpty([Col_E]), [Col_B]-[Col_D], [Col_E])

IIF(IsEmpty([Col_F]), Min([Col_C], [Col_E]), [Col_F])

******

If it can help you get your want output, please mark it as s solution and give a like to share more.

nghieu · Answer

Hi Chris,

Thank you very much for the suggestion. I tried your approach but did not get the correct results (as shown in the 2nd photo).

There is issue with Multi-row Formula tool for Column D from the 3rd record onward:

* I think that the Multi-row Formula calculates value in column D for all records in one-go and before the values in column F are calculated. Note that Column D of the current record = the sum of Column A, D and F of the previous record
* In the 2nd record, the value in Column F is not calculated yet and hence it is 0. Therefore, the value in Column D of the 3rd record is incorrect. The same issue happens to the subsequent records

I hope the explanation is clear.

ChrisTX · Answer

Add tools in this order:

For column D, use the Multi-Row Formula tool.  Under the Preparation tab, single-left-click the Multi-Row Formula tool, click Open Example, run the example workflow and review the examples.

For column E, use a Formula tool: [column B] - [column D]

For column F, use a Formula tool: Min([column C], [column E])

Here's a list of all functions:  https://help.alteryx.com/current/designer/functions

And here is the Tool Mastery index: Tool Mastery Index - Alteryx Community

Chris