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 LocationJan-24Feb-24Mar-24….Mar-25X782896111467623Y586361126297175Z355195759183965 Data Set B LocationB Jan-24B Feb-24B Mar-24….B Mar-25X9286875879857Y491656521836825Z275570696736134 Result should be (month wise subtract Data B from A) LocationJan-24Feb-24Mar-24….Mar-25X69028-647-331566Y95-295-395-539-650Z80-37563-553831

Subtract multiple columns dynamically

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

Transformation

Accepted answers

binu_acs

@Ronit_Gupta one way of doing this

Dynamic_substraction_22042025.yxzp

All comments

GrowthNatives

Hey Ronit Gupta,

To achieve the month-wise subtraction (A - 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

Input Both Data Sets
Use two Input Data Tools to load:
- Data Set A (Location + monthly values)
- Data Set B (Location + B monthly values)
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.
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)
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.

binu_acs

@Ronit_Gupta one way of doing this

Dynamic_substraction_22042025.yxzp

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.

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.
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.
Apply a Formula Tool:
- Create a new field to calculate the difference:

[2023] - [2022]&#8203;

Optionally, calculate % change:

([2023] - [2022]) / [2022] * 100&#8203;

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

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

binu_acs

@Ronit_Gupta make sure the dynamic select has the below expression

Ronit_Gupta

@binu_acs 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.

binu_acs

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.

录制_2025_04_23_11_38_18_452.gif

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703