topic Difference between each points in Discussions

Difference between each points

pankajsync — Sun, 28 Apr 2024 22:49:42 GMT

I would like to create a new column which shows the difference between the two values as highlighted (Column = Value). I need help to come up with a formula in that new column which shows the difference between blue point and red point for each Abs[Voltage] and for each Site and Coupon. Attaching the jmp file.

Re: Difference between each points

txnelson — Mon, 29 Apr 2024 01:08:24 GMT

Here is the formula that I came up with.....others may have alternate solutions.

theDif = Col Max( :Value, :Site, :"Abs[Voltage]"n ) - Col Min( :Value, :Site, :"Abs[Voltage]"n );
If( :Value < Col Mean( :Value, :Site, :"Abs[Voltage]"n ),
	theDif = theDif * -1
);
theDif;

Re: Difference between each points

jthi — Mon, 29 Apr 2024 05:49:53 GMT

For formulas there are many different kinds of Col formula combinations you can use, Jim suggested one and here is another which might work (add Abs/swap order as needed)

Col Mean(If(:EPG == "TE", :Value, .), :Coupon, :Site, :"Abs[Voltage]"n) - Col Mean(If(:EPG == "TP", :Value, .), :Coupon, :Site, :"Abs[Voltage]"n);

I know you asked for a formula, but if you can split your data this is much simpler to do as you can just add new difference column as you would be comparing row by row. In this case you might want to remove duplicate values first to avoid some possible issues. Depending on your data you can do this from rows menu and select duplicates -> remove them or create new summary table.

Re: Difference between each points

pankajsync — Wed, 04 Sep 2024 20:42:38 GMT

Using anything with COL MEAN, hangs/crashes jump often. My file has about 100k of rows. Is there any other solution which is more elegant?

Re: Difference between each points

txnelson — Wed, 04 Sep 2024 22:05:59 GMT

Split value by EPG grouping by site and Abs(voltage).

There will then be 2 columns. One for TE Value and one for TP Value.

Create a new column with a formula that calculates the difference.

Finally, go back to the original data table and using Tables=>Update, join the calculated table to the original table, matching on Site and Abs(Voltage)

The difference values will be replicated to both the TE and TP rows for each Abs(voltage)/Site row.