Subtract Columns from each other dynamically

[bigdatadan23]

Hello Alteryx Forum,

I am trying to figure out a solution to this and was wondering if possible. Right now I have this data. 

2024_05_13	2024_05_20	2024_05_27	C_Value
2472	2547	2573	2502
1821	1785	1793	1785

I would want to subtract "C_Value" from "2024_05_27", but also want the operation to work dynamically when the penultimate field name changes when the week changes. Is there any way to achieve this?

Thanks in advance

[tim-regas]

@bigdatadan23 Assuming you are always looking to subtract C_Value from the most recent column of data. I would use the dynamic rename tool to rename the most recent data to a static name and then perform the subtraction using the dynamic column name.

[leozhang2work]

Given the first few columns are the same type, another way to go would be pivot long those.

Then apply your dynamic logic through a filter tool, join/append back the c_values.

It is easier to implement the logic on rows rather than columns, and you can pivot the results back to columns if it is required in that form.

[danilang]

Hi @bigdatadan23 

Similar in concept to @tim-regas solution, but somewhat simpler in execution

This uses a Field Info tool to build a new name for the penultimate column which is changed to  __RenamedColumn__ by the first Dynamic Rename.  The formula tool subtracts C_Value from this and the second Dynamic Rename restores the original column names

Dan

 

[jdminton]

Based on @leozhang2work 's suggestion, you could do something like this to make it dynamic.