nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to recall a aggregated value (mean/Max etc) based on a grouped column in Formula or in For-Loop

HSS

Hi All,
I am trying to find out number of times alarm trigged. I have created a column "Number of Occurrence" based on simple logic -- If Data in Row () > Row()+1, trigger re-seted. But I am getting lot of false alarm, as seen in the image. The new login I want to implement -

If (
(Row() - Row()+1) >= Abs(Max(Data))/10, Reset the trigger, otherwise live it as it is
)

Something like that.

I am able to do it in a bit log way, like making summary table and then adding column from summary table and then comparing each value with new column. It works fine but making my script slow.

Is there any better way to implement it within formula or in For loop ?

Any help.
Thanks.

DD.jmp

Accepted answers

txnelson

I have come up with a formula that appears to work.........

There is a discrepancy with your No. of Occurrence - Corrected. On Row 32, I believe .13 - 08 results in .05 which is greater than .034, which should increase the number of occurrence.

Your implementation of an additional occurance is also not applied until the row after it is discovered. Therefore, using your formula has to have a little trickery put into it, to not apply the change until the next row is processed. Below is my formula that I used for "Jim's Calculation" column

If( Row() == 1 | :ID != Lag( :ID ) | :"Sub-set"n != Lag( :"Sub-set"n ),
	hold = :Number of Occurance;
	applyRow = 0;
);
If( :Data - Lag( :Data, -1 ) >= Abs( Col Max( :Data, :ID, :"Sub-set"n ) ) / 10,
	applyRow = Row() + 1
);
If( Row() == applyRow,
	applyRow = 0;
	hold = hold + 1;
);
hold;

DD Jim's update.jmp

txnelson

1. How is the number of occurrences calculated from the first 3 columns? It can easily be bypassed if you can determine what the value should be.

2. JMP will get confused with a column name of Sub-set. JMP will look at the value and try to subtract the value of set from the value of Sub. The notation of :"Sub-set"n tells JMP to use the whole string as the column name.

All comments

jthi

You can use Col Max() function to get maximum value within groups and Lag() to compare between rows in column.

Could you provide datatable with "correct" results to make it a bit easier to understand the logic behind alarms? Or do just just want to check if current Data value is larger than previous value within group (ID and Sub-set)?

HSS

Hi @jthi

I have added a column - "No. of Occurrence - Corrected". Which shows the correct (desired) results.

DD updated.jmp

jthi

You can calculate 10% Max directly with formula:

Col Max(:Data, :ID, :"Sub-set"n)*0.1

I'm still not sure how the occurrences should be counted, because it seems to never reset, but might move back by 1 value. To check values data column values which are after "current row" you can use Lag(:Data, - 1)

The rule you have would look something like this as one formula:

If(:Data - Lag(:Data, -1) >= Abs(Col Max(:Data, :ID, :"Sub-set"n)) / 10,
	1,
	0
)

HSS

It is not working. Somehow I am not able to implement - "Col Max(:Data , Group by{ID, Sub-set})" something like this.

I am able to call value from summary table to the column formula of main table, it is better than my original solution.

Thanks.