title: Lookups in the data.tables package using current record value as a reference?
body: <p>Recently started using the datatables package, and am just having some trouble with a lookup. Here is the data:</p>
  
  <pre><code>     Date MonthNo Unique Items Amounts Total
   1:   Jan       1    AAA     x      10    10
   2:   Jan       1    BBB     y       2     0
   3:   Feb       2    CCC     x       3     3
   4:   Feb       2    DDD     y      15     0
   5: March       3    AAA     y      20     0
   6: March       3    BBB     x      35    35
   7: April       4    CCC     x      15    15
   8: April       4    AAA     y      50     0
   9:   May       5    BBB     x      60    60
  10:   May       5    CCC     y      70     0
  11:  June       6    DDD     x     100   100
  12:  June       6    AAA     y      20     0
  </code></pre>
  
  <p>And basically, I want to create a new column called PYTD which is basically the total for each Unique for each month, but just for the prior month.
  eg:</p>
  
  <pre><code>    Date MonthNo Unique Items Amounts Total  PYTD
  
   7: April       4    CCC     x      15    3
  </code></pre>
  
  <p>This is the code I have so far:</p>
  
  <pre><code>Sys.setlocale("LC_CTYPE", "en_US.UTF-8")
  library(data.table)
  data &lt;- read.csv("sample.csv")
  df &lt;- as.data.frame(data)
  #str(df)
  dt &lt;- data.table(df)
  dt
  #str(dt)
  dt$Total = ifelse(dt$Items == "x",dt$Amounts,0)
  
  dtgrouped2 = dt[, lapply(.SD, sum, na.rm=TRUE), by=list(MonthNo,Unique),
                  .SDcol=c("Total")]
  
  dtgrouped2$PYTD &lt;- dtgrouped2[MonthNo == (dtgrouped2$MonthNo-1)
                                    &amp; Unique == dtgrouped2$Unique,Total]
  </code></pre>
  
  <p>But the dtgrouped2$PYTD is just giving me NAs unfortunately. </p>
  
  <p><strong>This is the final result I'm looking for</strong>:</p>
  
  <pre><code>   MonthNo Unique Total PYTD
   1:       1    AAA    10   NA
   2:       1    BBB     0   NA
   3:       2    CCC     3   NA
   4:       2    DDD     0   NA
   5:       3    AAA     0   10
   6:       3    BBB    35    0
   7:       4    CCC    15    3
   8:       4    AAA     0    0
   9:       5    BBB    60   35
  10:       5    CCC     0   15
  11:       6    DDD   100    0
  12:       6    AAA     0    0
  </code></pre>