Dividing rows based on different fields

Question

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

InputTable.xlsx

OutputTable.xlsx

david_fetters · Accepted Answer

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 Answer

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