Let’s say you have a sales data like the below.

And you want to calculate the cumulative sum of the revenue for each customer.

This is pretty simple.

You can use Group By command to group the data by customer id.

Then, select ‘Create Window Calculations’ -> Cumulative -> Sum (Total) from the column header menu of the ‘revenue’ column.

You will get the cumulative sum calculations for each customer.

What if you want the cumulative sum calculated only for the first 5 years?

Some customers have only a few years of the sales records but some have more than 5 years.

Now, let’s say we want the cumulative sum calculated only for the first 5 years.

The most simple way to do this is to take the following two steps.

  1. Create a column that indicates whether the sales happens within the first 5 years for each customer’s sales history.
  2. Do the cumulative sum calculation (cumsum) only when the above is TRUE.

1. Create a column that indicates whether the sales happens within the first 5 years for each customer’s sales history

Select ‘Create Calculation (Mutate)’ to open Mutate dialog.

Type the following calculation.

Order_Date <= min(Order_Date) + years(5)

‘min(Order_Date)’ returns minimal value of Order_Date column for each customer. ‘years(5)’ returns 5 years.

So, min(Order_Date) + years(5) returns the date that is after 5 years since the first date of sales for each customer. If the first date of the sales is 2010-02-01, then it will be 2015-02-01.

And ‘Order_Date <= min(Order_Date) + years(5)’ is evaluating each row if it happened in the first 5 years or not.

Do the cumulative sum calculation (cumsum) only when the above is TRUE

Add another calculation in the same Mutate command step, and type the following.

ifelse(within_5yrs, cumsum(revenue), NA)

This would execute ‘cumsum(revenue)’ only when the value of ‘within_5yrs’ is TRUE, otherwise it will keep it as NA.

What if I want to fill NA with the 5 years cumsum value?

You might want to fill NA with the 5 years cumsum values that have just been calculated.

This is actually pretty straightforward.

Select ‘Replace / Fill / Convert Data’ -> ‘Fill NA with Previous / Next Row’ from the column header menu.

Once you click ‘Run’ button, all the NA will be filled by the previous non-NA, which is the 5 years cumsum value, for each customer.