Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Dividing rows based on different fields

7 - Meteor

Hi all,


My input table can be found in the attached excel file. 'InputTable.xlsx'. What I want to do with the table is to make a division per datalabel and company, dividing the data value of 2020 by the value of 2017. So for example for DataLabel 'X' and Company 'AA', I want to divide 100 by 80, resulting in 1.25. Doing this for all datalabels and companies, should result in the table as in 'OutputTable.xlsx'. I know I can just write an R-script and use that in my workflow, however, I was wondering whether there is a way to do this in Alteryx?


Hopefully there is! Thanks in advance for your response(s).





11 - Bolide

Here's a solution. We join all the rows to one another based on Company, DataUnit, and DataLabel.  We then use filters to remove duplicate rows so we end up with one row that has both the Base Year and End Year values on it.  Then just a simple formula to the division and rename a couple of fields.


Let me know if this makes sense after you get a chance to see the values at each step.

13 - Pulsar

You can achieve the results using multi row formula tool...



7 - Meteor

Hi @david_fetters and @ponraj,


Thanks a lot for your clever solutions! Both solutions give the desired result. I've decided to implement @david_fetters's solution  since it is a better fit for my workflow.