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

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

HSS — Fri, 09 Jun 2023 18:07:31 GMT

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.

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

jthi — Wed, 15 Dec 2021 10:29:13 GMT

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)?

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

HSS — Wed, 15 Dec 2021 10:46:49 GMT

Hi @jthi

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

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

jthi — Wed, 15 Dec 2021 13:50:06 GMT

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
)

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

HSS — Thu, 16 Dec 2021 04:37:21 GMT

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.

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

txnelson — Thu, 16 Dec 2021 05:51:22 GMT

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;

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

HSS — Thu, 16 Dec 2021 06:22:37 GMT

Hi @txnelson, It worked. many thanks.

Few things -
1. Your formula is working. But it is taking "No. of Occurrence" as a input for "hold" in row number 2. Is there any way we can bypass that column. (let say, I do not have my original "No. of Occurrence" column!). Can we get correct No. of Occurrence based on just first 3 columns ? I do not want to depend upon that columns, if tomorrow I found some other type of false alarm, not covered here !

2. What is 'n' next to "Sub-set" -- "Sub-set"n ? I did not get it from Jim's suggestion as well. Any reference for it ?

3. At Row 32 Occurrence should increased. I did changed few values manually and it was my mistake.

Thanks again.

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

txnelson — Thu, 16 Dec 2021 06:40:52 GMT

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.

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

HSS — Thu, 16 Dec 2021 06:42:48 GMT

@txnelson
I understand that my starting point for for No. of Occurrence is not same every set of data. (Some times it is 1 and sometimes it is 2 and so on). So it is not possible to eliminate that column fully. But just as dummy column I can replace first "hold =1" and then it works as a counter.

but my other question is still need some help --

1. What is 'n' next to "Sub-set" -- "Sub-set"n ? I did not get it from Jim's suggestion as well. Any reference for it ?

2. and one more - I think
Dif(:Data) == :Data - Lag(:Data, -1) -- is this correct ?

Is there any benefit using one over the other ?

Thanks again.

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

txnelson — Thu, 16 Dec 2021 06:49:54 GMT

The Scripting Guide found in the JMP Documentation Library, available under the Help pull down menu documents the "name"n reference

Names
A name is simply something to call an item. When you assign the numeric value 3 to a variable
in the expression a = 3, a is a name.
Commands and functions have names, too. In the expression Log( 4 ), Log is the name of the
logarithmic function.
Names have a few rules:
• Names must start with an alphabetic character or underscore and can continue with the
following:
– alphabetic characters (a-z A-Z)
– numeric digits (0-9)
– whitespace characters (spaces, tabs, line endings, and page endings)
– mathematical symbols in Unicode (such as the Greek small alpha letter α)
– a few punctuation marks or special characters (apostrophes (‘), percent signs (%),
periods (.), backslashes (\), and underscores (_))
• When comparing names, JMP ignores whitespace characters (such as spaces, tabs, and line
endings). Upper case and lower case characters are not distinguished. For example, the
names Forage and for age are equivalent, despite the differences in white space and
case.
You can still have a name that is any other sequence of characters. If the name does not follow
the rules above, it needs to be quoted and followed by "n". For example, to use a global
variable with the name taxable income(2011), you must use "taxable income(2011)"n
every time the variable appears in a script:
"taxable income( 2011 )"n = 456000;
tax = .25;
Print( tax * "taxable income( 2011 )"n) ;
114000

If you are going to get into JSL, then you really need to read the Scripting Guide, cover to cover