Subtract multiple columns dynamically

[Ronit_Gupta]

I have data set with Location and sales value coming from two different sources. I need to report gap for these values. For example,

Data Set A

Location	Jan-24	Feb-24	Mar-24	….	Mar-25
X	782	896	111	467	623
Y	586	361	126	297	175
Z	355	195	759	183	965

Data Set B

Location	B Jan-24	B Feb-24	B Mar-24	….	B Mar-25
X	92	868	758	798	57
Y	491	656	521	836	825
Z	275	570	696	736	134

Result should be (month wise subtract Data B from A)

Location	Jan-24	Feb-24	Mar-24	….	Mar-25
X	690	28	-647	-331	566
Y	95	-295	-395	-539	-650
Z	80	-375	63	-553	831

[GrowthNatives]

Hey Ronit Gupta,

To achieve the month-wise subtraction (A - B) between two datasets like in your image, here's how you can handle it in Alteryx step-by-step:

Step-by-Step Solution in Alteryx
 

1.  Input Both Data Sets
   Use two Input Data Tools to load:
   
   • Data Set A (Location + monthly values)
   • Data Set B (Location + B monthly values)
2. Prepare for Join (Rename B Columns)
   • Use a Dynamic Rename Tool on Data Set B to:
     • Remove the “B ” prefix from each month (e.g., B Jan-24 → Jan-24).
       • Set Rename Mode to "Take Field Names from First Row".
       • Or use a Formula to remove "B " from header names.
3. Join Both Data Sets
   • Use a Join Tool:

     • Join on Location.

     • This will give you:

       • Left: Data Set A

       • Right: Data Set B (renamed to match A)

4. Create Calculated Fields
   
   • Use a Formula Tool to subtract values from each month:

[Jan-24] - [Jan-24 (Right)]
[Feb-24] - [Feb-24 (Right)]
...
[Mar-25] - [Mar-25 (Right)]

• Rename each resulting column to reflect the result.
•  Optional: Clean Up

  • Use the Select Tool to drop unnecessary original columns if needed.

  • Output the final result using the Browse or Output Data Tool.

Hope this solution helps you make the most of Alteryx! If it did, click 'Mark as Solution' to help others find the right answers.

💡Found it helpful? Show some love with kudos as your support keeps our community thriving!

🚀Let’s keep building smarter, data-driven solutions together! 🚀

[Ronit_Gupta]

@GrowthNatives , I have data from 2022 that needs to be analyzed. Is there a dynamic way to compare the two columns? Also it needs to be done with other key figures. 

[binuacs · Accepted solution]

@Ronit_Gupta one way of doing this

[GrowthNatives]

If you need to compare year-over-year data (like 2022 vs 2023) across multiple key figures dynamically, here's a simple and scalable approach in Alteryx.

1. Reshape the data (if needed):
   • Use the Transpose Tool to convert wide format (e.g., Sales_2022, Sales_2023) into long format with columns like Metric, Year, and Value.
2. Use the Cross Tab Tool:

   • Group by Location and Metric.

   • Set Year as column headers and Value as data values.

   • This creates side-by-side year columns like 2022, 2023.

3. Apply a Formula Tool:
   • Create a new field to calculate the difference:

[2023] - [2022]​

• Optionally, calculate % change:

([2023] - [2022]) / [2022] * 100​

This method works across all metrics dynamically and avoids manual handling of each column.

Hope this solution helps you make the most of Alteryx! If it did, click 'Mark as Solution' to help others find the right answers.

💡Found it helpful? Show some love with kudos as your support keeps our community thriving!

🚀Let’s keep building smarter, data-driven solutions together! 🚀

[Ronit_Gupta]

@binuacs  thanks, I am stuck at Diff macro. Unable to get location field as output

[binuacs]

@Ronit_Gupta make sure the dynamic select has the below expression

[Ronit_Gupta]

@binuacs Yes, select tool is the same but maybe input needs to be changed. Input for macro needs to be static file? it is output of join tool.

[binuacs]

If your column names are different then update the macro accordingly, for example if column Location is different name in your input file you need to update the macro or provide actual column names I will update the flow 

[flying008]

Hi, @Ronit_Gupta 

The other dynamic solution for you.