445206_en-US

liliconcom

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

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.

 

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

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

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

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

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.