community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Applying sumproduct on dynamic columns in alteryx

Atom

Hello All

 

I am new to alteryx and facing this issue in replicating the sumproduct formula in alteryx on dynamically generated columns. Here is the example of the table I am working with.

ProductCapacityType of processSC ProcessIDAligned NameLocationCountryRegion2016_CAPProd1 CostProd2CostProd1Prod2
SA300000On-purposeMC544AbcbChinaasia300    

 

The columns named Prod1Cost and Prod2Cost are dynamic, there can be multiple products and hence their cost columns and same with the corresponding Prod1, Prod2 columns. I want to do a dynamic sumproduct which would multiply the Prod1Cost with Prod1 and Prod2Cost with Prod2 and then sump. In excel I used to just put a formula sumproduct (A4:D4,E4:H4), I had taken 4 columns as buffer in excel. How can I do this in alteryx? All help is appreciated, and happy to explain more.

Alteryx Certified Partner
Alteryx Certified Partner

Here's how I would resolve the issue.

 

This will only work if the Cost field always has some text within the header that says "Cost".

 

I essentially transpose your data and use some text transformation to understand whether it is a cost column or a 'value' column (i.e. the 2nd column). I then use regex to pull out the appropriate ID so we can link them together.

I perform a cross tab which then gives me two new fields one cost, one value.

We multiply them together and then use the summerize to calculator the sum of our multiplied value.

 

Example attached.

Ben

Atom

Thank you for your reply Ben. Could you just explain the use of regex here, my headers would definitely have "Cost" attached but not necessarily the numerical values like 1 or 2 (that was just for example). would this work even then?

Alteryx Certified Partner
Alteryx Certified Partner

Hi Winnie,

 

It is my understanding that you would like to multiply the associated columns before summing the resulting value.

For example, prodcost1 = 20, prod1 = 10 so we do 20*10, we then repeat this step for the 2nd value and sum them together.

The way this works in my workflow is the regex is used to identify which aspects/pairs of values to multiply together. We would need someone of understanding this, not neccessarily through regex for this to work, perhaps even column position within the given group of values (i.e. cost v units).

Would you be able to post a more representative sample table and we can look to develop a solution off this.

 

Ben

Labels