Conditional SUMIF Calculation in Alteryx

Question

Please refer the Data file attached.

I need to find the highlighted value of Required Qty column. All other values I have got.

The problem is below formula is applying in same column in which we need the result.

=SUMIFS(AA:AA,F:F,"<"&F5,G:G,E5)+(SUMIFS(AA:AA,F:F,"<"&F5,G:G,E5)*U5/100)

It's kind of Cumulative sum but Additional condition. -:

The condition from above formula is to get required values (Required Qty) I need Sum of all Required Qty where other BOM levels are  Current BOM level.  Also another condition is Where ever BOM material match with BOM Component. then only it should sum with other reuqired Qty where Current BOM level > BOM level where BOM material Matched with Component

Please help me guys figuring out this logic. I have been trying hard to get it.

Book1.xlsx

prakhar021 · Answer

The conditions are simple. For Example I need to find the values 106237 which is first values that is highlighted. The condition is

First it will match the BOM material of the particular row with the component. Second it will compare the BOM level of this particular row with the BOM level which are less than the current BOM level of this particular records. If these two conditions are met then it will sum the Required Qtys of those records, and then as per formula -:(103900+254)+(103900+254)*2/100

The problem is this approach needs to be built in a cumulative way. Like for other highlighted records as well. Currently those records hold null value.

I have tried multiple things with join and summerize tool. If possible can help me build a workflow

EKasminsky · Answer

You will need to use the Summarize tool for this. Find the Sum of Required Qty seperate. Use another Summarize Tool and Group by COM Component and Sum of Required Qty. Call it something like Required Qty COM Equal or something. Join those together to everything.

https://help.alteryx.com/current/en/designer/tools/transform/summarize-tool.html

I don't really understand the logic of the conditions, can you elaborate further?