How to subtract values from previous column to values in new column

[blitz45]

Hi! 

I created and have been trying to automate a report every month on incentives in alteryx.

I've been trying to figure out the best way in subtracting values from a previous column to the new appended column. 

I have attached an example of an appended monthly report. If you look at the data from right to left, I want to create a formula that will always subtract the column 4 and column 1.

Is there anyway we could do that in Alteryx?

[fmvizcaino]

Hi @blitz45 ,

Is it a dynamic column? Would you be able to share a small dataset with your desired output, please?

Best,

FErnando Vizcaino

[seven · Accepted solution]

Hi @blitz45 

here you go! Since the relevant fields are always the last and the 3rd from the last, I created a way to number them, apply a formula, and then rename them to the original names. Workflow attached and image enclosed.

By the way, you didn't make it clear if the formula should be 1 - 4 or 4 - 1. I wrote 1 - 4. You can change the formula to suit your needs.

[blitz45]

Here's an example of what I want for my output highlighted in yellow. As you can see I have subtracted 6875 - 5589 = 1286. If you look at the data from right to left, the column I will always need to subtract is column 4 and 1 (counting from the right). I hope that makes sense. 

[blitz45]

Somehow i'm unable to open your workflow. Can you try to describe what you have in your workflow? I tried going off by what you had in your screenshot but I am missing some details and I'm unable to run it. Any help would be awesome! Thank you so much!

[seven · Accepted solution]

I attached a packaged workflow earlier. Not sure why you can't open it. I have attached a workflow here. What version of Alteryx are you using?

[seven · Accepted solution]

Here is the recipe:

1. Input data
2. Field Info
3. RecordID
4. Sort - RecordID Descending
5. Select - Uncheck RecordID
6. RecordID
7. Dynamic Rename - Left side has Input Data, Right side has RecordID
8. 
9. Formula - Create a new field with a Double data type and an expression of [4] - [1]
10. Dynamic Rename - Left side has Formula, Right side has RecordID
11. 

That is how I built this. This recipe and the workflow image will get you to the solution.

[blitz45]

It worked! Thank you!

[seven]

Please mark the answer as a solution. Thank you.