This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
Type of process
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.
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.
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?
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.