Difference between 2 consecutive values in Kusto-CodePudding

I have the following script:

let StartTime = datetime(2022-02-18 10:10:00 AM);
let EndTime = datetime(2022-02-18 10:15:00 AM);
MachineEvents
| where Timestamp between (StartTime .. EndTime)
| where Id == "00112233" and Name == "Higher"
| top 2 by Timestamp
| project Timestamp, Value

I got the following result:

What I am trying to achieve after that is to check if the last Value received (in this case for example it is 15451.433) is less than 30,000. If the condition is true, then I should check again the difference between the last two consecutive values (in this case : 15451.433 - 15457.083). If the difference is < 0 then I should return the Value as true, else it should return as false (by other words the Value should give a boolean value instead of double as shown in the figure)

CodePudding user response：

datatable(Timestamp:datetime, Value:double)
[
 datetime(2022-02-18 10:15:00 AM), 15457.083,
 datetime(2022-02-18 10:14:00 AM), 15451.433,
 datetime(2022-02-18 10:13:00 AM), 15433.333,
 datetime(2022-02-18 10:12:00 AM), 15411.111
]
| top 2 by Timestamp
| project Timestamp, Value
| extend nextValue=next(Value)
| extend finalResult = iff(Value < 30000, nextValue - Value < 0, false)
| top 1 by Timestamp
| project finalResult

Output:

finalResult
1

CodePudding user response：

You can use the prev() function (or next()) to process the values in the other rows.

...
| extend previous = prev(value)
| extend diff = value - previous
| extend isPositive = diff > 0

You might need to use serialize if you don't have something like top that already does that for you.