Difference between two columns

[Ariz]

Hi all

Please can anyone advise which formula should I use to calculate difference between two columns in a new column.

thanks

Ariz 

[Jean-Balteryx]

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

[Jonathan-Sherman]

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

[Ariz · Accepted solution]

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 

[Jonathan-Sherman · Accepted solution]

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

[Ariz]

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 

[Jonathan-Sherman · Accepted solution]

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

[Ariz]

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 

[Jonathan-Sherman]

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

[rohit782192]

How we would calculate the difference of the cell1 and cell7 in alteryx.

cell 1 and cell 7 are at different posotion.