Difference between two columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi all
Please can anyone advise which formula should I use to calculate difference between two columns in a new column.
thanks
Ariz
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Ariz ,
If both columns are numeric you can do [Column1]-[Column2] in the formula and specify your new column's name.
Do you have a workflow to share ?
Cheers,
Jean-Baptiste
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You'll want to create an expression in a formula tool of:
[Column1Name] - [Column2Name]
Ensure you click "+ new column" on the dropdown (highlighted below) to create a new column, and store it as a numeric data type (double for decimals, int for whole numbers)
If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.
Regards,
Jonathan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks Jonathan
It did work but can see that if there is blank cell in one of the columns it did not calculate.
Any thoughts please ?
thanks in advance
Ariz
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Ariz,
In that case you will want to change the formula to:
IF IsNull([Value1]) THEN 0 ELSE [Value1] ENDIF - IF IsNull([Value2]) THEN 0 ELSE [Value2] ENDIF
OR
IF IsEmpty([Value1]) THEN 0 ELSE [Value1] ENDIF - IF IsEmpty([Value2]) THEN 0 ELSE [Value2] ENDIF
Depending on how Alteryx is treating your blank cells, if they come through as Null then use the first formula, if they come through as blank or empty then use the second.
If this solves your issue please mark the answer as correct, if not let me know!
Regards,
Jonathan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks Jonathan it worked!
Also please can you help with Total of each columns, what would be the best way to calculate the total in the last row of each column?
thanks
Ariz
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Ariz,
It sounds as though you're trying to achieve something along the lines of this?
I've summarised to SUM up the three numeric columns and unioned it back to the original data set to give me a final row with the total values.
If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.
Regards,
Jonathan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Jonathan,
If there is no data for one of the columns
like below Current or Prior i am getting
New -so this this time data for Apple is missing for Current
IF IsNull([Current]) THEN 0 ELSE [Current] ENDIF - IF IsNull([Prior]) THEN 0 ELSE [Prior] ENDIF
Any thoughts please ?
thanks
Ariz
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Ariz,
I'd union a data set to scaffold such as the below:
This would then ensure the column exists to allow the formula to be calculated correctly. The record can be removed at the end of the workflow with a filter tool
If this solves your issue please mark the answer as correct, if not let me know! I've attached an updated workflow for you to download if needed.
Regards,
Jonathan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
How we would calculate the difference of the cell1 and cell7 in alteryx.
cell 1 and cell 7 are at different posotion.
