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

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

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

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

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;