Cumulative sum in a window (or running window sum) based on a condition in R : <p>I am trying to calculate cumulative sum for a given window based on a condition. I have seen threads where the solution does conditional cumulative sum (<a href="https://stackoverflow.com/questions/32097646/calculate-a-conditional-running-sum-in-r-for-every-row-in-data-frame">Calculate a conditional running sum in R for every row in data frame</a>) and rolling sum (<a href="https://stackoverflow.com/questions/24397299/rolling-sum-by-another-variable-in-r/24400600#24400600">Rolling Sum by Another Variable in R</a>), but I couldn't find the two together. I also saw that <code>data.table</code> doesn't have a rolling window function at <a href="https://stackoverflow.com/questions/11676476/r-data-table-sliding-window">R data.table sliding window</a>. So, this problem is very challenging for me.</p>

<p>Moreover, the <a href="https://stackoverflow.com/a/24400600/5713272">solution posted by Mike Grahan</a> on rolling sum is beyond my comprehension. I am looking for <code>data.table</code> based method primarily for speed. However, I am open to other methods if they are understandable. </p>

<p>Here's my input data:</p>

<pre><code>DFI &lt;- structure(list(FY = c(2011, 2012, 2013, 2015, 2016, 2011, 2011, 
2012, 2013, 2014, 2015, 2010, 2016, 2013, 2014, 2015, 2010), 
    Customer = c(13575, 13575, 13575, 13575, 13575, 13575, 13575, 
    13575, 13575, 13575, 13575, 13578, 13578, 13578, 13578, 13578, 
    13578), Product = c("A", "A", "A", "A", "A", "B", "B", "B", 
    "B", "B", "B", "A", "A", "B", "C", "D", "E"), Rev = c(4, 
    3, 3, 1, 2, 1, 2, 3, 4, 5, 6, 3, 2, 2, 4, 2, 2)), .Names = c("FY", 
"Customer", "Product", "Rev"), row.names = c(NA, 17L), class = "data.frame")
</code></pre>

<p>Here's my expected output: (Manually created; My apologies if there is a manual error)</p>

<pre><code>DFO &lt;- structure(list(FY = c(2011, 2012, 2013, 2015, 2016, 2011, 2012, 
2013, 2014, 2015, 2010, 2016, 2013, 2014, 2015, 2010), Customer = c(13575, 
13575, 13575, 13575, 13575, 13575, 13575, 13575, 13575, 13575, 
13578, 13578, 13578, 13578, 13578, 13578), Product = c("A", "A", 
"A", "A", "A", "B", "B", "B", "B", "B", "A", "A", "B", "C", "D", 
"E"), Rev = c(4, 3, 3, 1, 2, 3, 3, 4, 5, 6, 3, 2, 2, 4, 2, 2), 
    cumsum = c(4, 7, 10, 11, 9, 3, 6, 10, 15, 21, 3, 2, 2, 4, 
    2, 2)), .Names = c("FY", "Customer", "Product", "Rev", "cumsum"
), row.names = c(NA, 16L), class = "data.frame")
</code></pre>

<p><strong>Some commentary about the logic:</strong></p>

<p>1) I want to find rolling sum in a 5-year period. Ideally, I would like this 5-year period to be variable i.e. something I can specify elsewhere in the code. This way, I have the liberty to vary the window later on for my analysis. </p>

<p>2) The end of Window is based on the maximum year (i.e. <code>FY</code> in example above). In above example, the max <code>FY</code> in <code>DFI</code> is <code>2016</code>. So, starting year of the window would be <code>2016 - 5 + 1 = 2012</code> for all entries in <code>2016</code>.</p>

<p>3) The window sum (or running sum) is calculated by <code>Customer</code> and for a specific <code>Product</code>. </p>

<p><strong>What I tried:</strong></p>

<p>I wanted to try something before posting. Here's my code:</p>

<pre><code>  DFI &lt;- data.table::as.data.table(DFI)

  #Sort it first
  DFI&lt;-DFI[order(Customer,FY),]

  #find cumulative sum; remove Rev column; order rows
  DFOTest&lt;-DFI[,cumsum := cumsum(Rev),by=.(Customer,Product)][,.SD[which.max(cumsum)],by=.(FY,Customer,Product)][,("Rev"):=NULL][order(Customer,Product,FY)]
</code></pre>

<p>This code calculates the cumulative sum, but I am unable to define 5-year window and then calculate running sum. I have two questions:</p>

<p><strong>Question 1)</strong> How do I calculate a 5-year running sum?</p>

<p><strong>Question 2)</strong> Can someone please explain <a href="https://stackoverflow.com/a/24400600/5713272">Mike's method on this thread</a> ? It seems to be fast. However, I am not really sure what's going on there. I did see that someone requested some commentary, but I am not sure whether it is self-explanatory.</p>

<p>Thanks in advance. I have been struggling on this problem for two days.</p>
