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

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?

example 1.jpg

Workflow

Developer

Tips and Tricks

Accepted answers

seven

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.

subtract_fields.yxzp

seven

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?

subtract_fields.yxmd

seven

Here is the recipe:

Input data
Field Info
RecordID
Sort - RecordID Descending
Select - Uncheck RecordID
RecordID
Dynamic Rename - Left side has Input Data, Right side has RecordID
Formula - Create a new field with a Double data type and an expression of [4] - [1]
Dynamic Rename - Left side has Formula, Right side has RecordID

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

All comments

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

Hi @blitz45

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.

subtract_fields.yxzp

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.

example 2_06.29.2020.jpg

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

Please mark the answer as a solution. Thank you.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15874

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703