Query Details
Running Total Aka Cumulative Sum
Query
let SampleData = datatable (user:string, rowValue: int) ["A",5,"B",12,"B",15,"A",3,"A",9,"A",19,"B",7];
SampleData
| serialize
| extend rowNumber = row_number()
| extend rowNumberCurrentUser = row_number(1, prev(user,1,0) != user)
| extend previousValue = strcat("Previous value was ", prev(rowValue,1,0))
| extend nextValue = strcat("Next value was ", next(rowNumber,1,0))
| extend runningTotal = row_cumsum(rowValue)
| project rowNumber, rowNumberCurrentUser, user, rowValue, previousValue, nextValue, runningTotal
Explanation
The query takes a sample dataset with two columns: "user" and "rowValue". It then performs several operations on the dataset to add additional columns.
First, it serializes the dataset, which means it converts the dataset into a tabular format.
Next, it adds a column called "rowNumber" which assigns a unique number to each row in the dataset.
Then, it adds another column called "rowNumberCurrentUser" which assigns a unique number to each row for each user.
After that, it adds a column called "previousValue" which shows the previous value of "rowValue" for each row.
It also adds a column called "nextValue" which shows the next value of "rowNumber" for each row.
Finally, it adds a column called "runningTotal" which calculates the cumulative sum of "rowValue" for each row.
The query then projects (displays) the columns "rowNumber", "rowNumberCurrentUser", "user", "rowValue", "previousValue", "nextValue", and "runningTotal" in the output.
Details
Rod Trent
Released: March 26, 2020
Tables
Keywords
Operators