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):
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.
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
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 hope the explanation is clear.
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:
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.