Continues Threshold value crossed Counts

[Karthick30]

I have a Dataset like below. Threshold is 70. For each servers , if value is greater than 70 for continuously 4 times, I need to count that instance as 1.

Output required is INDHYAPP1 - 5/2/2022 - 1 count.

Need to count only continuous threshold pass. if it passed 70 for 2 times and reduced less than 70 means, I need to ignore that.

Any suitable way to achieve this

[chaorenzhu · Accepted solution]

Provided your Servers, Date and Time are sorted in ascending order (if not you can always use ORDER BY), first in your load script create a flag:

if(Value>70,1,0) as flag

Then do a resident load and use rangesum() to calculate cumulative sum for flag:

rangesum(flag,if(flag=0,0,peek(cumsum))) as cumsum

And in your output table, just calculate how many times cumsum=4 for a particular Servers, Date

count({<cumsum={4}>} cumsum)

[vinieme12]

what if it is higher than 70 , 8 times in a row? does the count reset to 1 after the 4th instance?

[chaorenzhu]

The count will only reset when an occurrence of <=70, but my set analysis will only pick up those instances where the count=4

[Karthick30]

Thanks @chaorenzhu . It worked for me