Showing results for 
Search instead for 
Did you mean: 

How do I calculate a running/cumulative total in Dataprep?

DataRobot Employee
DataRobot Employee

How do I calculate a running/cumulative total in Dataprep?

A question that has come up a few times over the last few months, is How can I calculate a running/cumulative total in Dataprep?


Let's look at two methods to achieve this:

  1. Clicking - Using Dataprep's Window/Aggregate function.
  2. Coding - Using Dataprep's Spark SQL function (and using the cumsum function)

The below dataset will be used in both examples:



Importing it into DataRobot Dataprep looks like this:



Now let's get started....


Method 1) Clicking – Windowing Aggregation

Once you have ensured you have a column to sort by (Sale Date in this example) and a column to compute the running total for (Sales/Returns in this example)

Step 1)  Add a Compute column by clicking the icon



Call the Column Dummy

Enter the value 1 (the value is of no consequence). The screen will look like this:


Step 2)  Add a Window -> Aggregate step by clicking





To show a cumulative total populate the window with:

  • Sum (from the drop down)
  • Sales/Returns (for the Column we’re making the calculation on)
  • Cumulative Total (for the new columns name)
  • Dummy as the Window Grouped by
  • Sale Date as the Window Sorted by
  • Starting from top of window
  • Ending at current row


Method 2) Coding– Spark SQL (cumsum)

Alternatively one can use the Spark SQL transformation by clicking the icon



(Note for convenience I renamed the columns to have no spaces or special characters).


The syntax I have used to use the Spark SQL cumsum function is:
SELECT SaleDate, SalesReturns, SUM(SalesReturns)

          over (ORDER BY SaleDate ROWS BETWEEN unbounded preceding AND CURRENT ROW ) cumsum

 FROM   dataset

 ORDER  BY SaleDate


Screenshot below:



And there you have it, two ways to calculate a running/cumulative total in DataRobot Dataprep!







Labels (2)
0 Replies