R: Aggregating Data for Previous 30 Days

Question

As a novice I was hoping to understand how to aggregate data using an arbitrary look back (eg previous 30 days from a date). See my data below as an example. I want to group by each name, and sum sales for the 30 days leading up to say 02-15-2019. So it will look back 30 calendar days from 02-15-2019 and give me the total sales by Name (eg Person 1 = $60; Person 2 = $30)

 Name      Date          Sales
Person1    01-31-2019    $10
Person1    02-15-2019    $50
Person1    06-18-2019    $100
Person2    01-31-2019    $25
Person2    02-15-2019    $5
Person2    06-18-2019    $200

Answer 1

Simple example (if I understood your question correctly):

library(dplyr) 
set.seed(123)
df <- data.frame(Name = sample(c("Person1", "Person2"), 6, T),
           Date = c("01-31-2019", "02-15-2019", "06-18-2019", "01-31-2019", "02-15-2019", "06-18-2019"),
           Sales = runif(6, 10, 100), stringsAsFactors = F)

df$Date <- lubridate::mdy(df$Date)

target <- lubridate::mdy("02-15-2019")
sales <- df %>% filter(between(Date, target - 30, target)) %>% 
  group_by(Name) %>% summarise(Sales = sum(Sales))

Answer 2

select Name ,sum(sales) from orders where DATEDIFF(day,OrderDate,GETDATE()) between 0 and 30 group by Name