CODEWITHSUNDEEP
azure-data-explorerkql
Kiran
Kiran

Reputation: 33

How to find the cumulative difference between two rows and display it accordingly in Azure Data Explorer

I have requirement where I need to calculate the cumulative difference between each row in Azure Data Explorer.

Below is the sample data

ID | NAME    | Value
---+---------+------------- 
 1 | ABC     | 1987698.987
 2 | DEF     | 1987870.987
 3 | GHI     | 1987990.987
 4 | JKL     | 1988200.987

Expected result Output:

ID | NAME  | Value       | Cumulative_difference
---+-------+-------------+----------------------
 1 | ABC   | 1987698.987 |      0  
 2 | DEF   | 1987870.987 |    172
 3 | GHI   | 1987990.987 |    120
 4 | JKL   | 1988200.987 |    210

Thanks in advance

Upvotes: 0

Views: 960

Answers (1)

Avnera
Avnera

Reputation: 7618

You can use the prev() function, for example

datatable(ID:int, NAME:string, Value:double)
 [1, "ABC", 1987698.987,
  2, "DEF", 1987870.987,
  3, "GHI", 1987990.987,
  4, "JKL", 1988200.987]
| order by ID asc 
| extend Cumulative_difference = coalesce(Value - prev(Value), double(0))
ID NAME Value Cumulative_difference
1 ABC 1987698.987 0
2 DEF 1987870.987 172
3 GHI 1987990.987 120
4 JKL 1988200.987 210

Upvotes: 2

Related Questions