Dividing rows based on different fields

[rvanderhave]

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).

Robbert

[david_fetters · Accepted solution]

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.

[ponraj · Accepted solution]

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

[rvanderhave]

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.