How to find Running Total in Kusto | Prev Function in Kusto Query | Kusto Query Language Tutorial

Topic: How to find Running Total in Kusto | Prev Function in Kusto Query. 

In this video we are going to learn about the previous function in Kusto, previous function returns the value of a specific column in a specified row the specified row is a specified offset from a current row in the serialized row set so there are three different options we have with the previous we can simply provide the column or we can provide the offset value as well or there is a third one if there is a default value you would like to put for empty values, also you are going to learn how to find or calculate the running total by entire table or by a specific group

 //prev() - Returns the value of a specific column in a specified row. The specified row is at a specified offset from the current row in a serialized row set.  
 //Syntax  
 //prev(column)  
 //prev(column, offset)  
 //prev(column, offset, default_value)  
   
 //create Table and Insert Sample Data in Azure Data Explorer DB for Testing  
 .drop table TotalSale  
 .create table TotalSale (  
   id: int   
   ,SalePersonFName: string  
   ,SalePersonLName : string  
   ,ProductName : string  
   ,ItemsSold : int  
   ,SoldPrice :real  
   ,SoldDate: date  
   ,City : string  
   ,State : string  
   ,Country : string  
   ,Region : string  
   )  
      

  
      //Insert data  
       .ingest inline into table TotalSale <|  
 11,Tamara,Tony,Cell Phone,2,1200,2015-03-03,Frankfurt,Hesse,Germany,Europe  
 9,Petra,Henry,TV,10,5000,2015-04-08,Paris,Île-de-France,France,Europe  
 3,Christy,Ladson,TV,3,1600,2015-04-02,High Point,NC,USA,North America  
 7,Chirag,Patel,Cell Phone,5,1500,2015-06-23,AhmadAbad,Gujrat,India,Asia  
 2,M,Raza,Cell Phone,2,800,2015-07-15,Charlotte,NC,USA,North America  
 5,Najaf,Ali,Computer,1,300,2015-06-20,Karachi,Sindh,Pakistan,Asia  
 6,Sukhjeet,Singh,TV,2,900,2015-06-21,ChandiGar,Punjab,India,Asia  
 4,John,Rivers,Laptop,5,2400,2014-03-09,Jersey City,NJ,USA,North America  
 8,Aleena,Aman,Laptop,2,800,2015-05-25,Lahore,Punjab,Pakistan,Asia  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  
 1,Aamir,Shahzad,TV,1,700,2015-07-15,Charlotte,NC,USA,North America  
 12,aamir,Shahzad,TV,1,7000,2015-07-15,Charlotte,NC,USA,North America  
 10,Rita,Roger,Laptop,7,2100,2015-04-11,Paris,Île-de-France,France,Europe  

   
 //using next() to get next value from row  
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,prev(id)   
 

  
   
 //using next() to get next value from row -- using offset  
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,prev(id,2)   
   
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2)   
 

  
 // using the third parameter to fill the blanks  
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2)   
   
 TotalSale  
 | order by id asc  
 | project id,SalePersonFName,SalePersonLName,prev(SalePersonLName,2,"Fill the Blanks")   
  

 
 // More real time scenario  
   
   
 //Running total by Region  
 TotalSale  
 | order by Region,ItemsSold  
 | project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,SoldDate,Region,RunningTotal=row_cumsum(ItemsSold)  
  

 
 // Getting Running total for Region base  
 TotalSale  
 | order by Region,ItemsSold  
 | project id,SalePersonFName,SalePersonLName,ItemsSold,SoldPrice,SoldDate,Region,RunningTotal=row_cumsum(ItemsSold, Region != prev(Region))  
     
   

Video Demo: How to find Running Total in Kusto | Prev Function in Kusto Query | Kusto Query Language Tutorial