1

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
camille
  • 16,432
  • 18
  • 38
  • 60
KevS
  • 59
  • 10
  • 1
    So subset the data to just between 02-15-2019 - 30? The output of calling `dput` on your data would be more helpful, since that will give dates in whatever format you're actually working with, as well as the sales amount if it's actually numeric (i.e. doesn't have `$` attached to it) – camille Sep 19 '19 at 21:20
  • Do you mean *"up to 02-15-2019"* for all rows, or 30 days prior to each row's `Date`? – r2evans Sep 19 '19 at 21:30

2 Answers2

2

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))
slava-kohut
  • 4,203
  • 1
  • 7
  • 24
  • This looks like this could work. I tested it out using your example and it seems to do the right things. One quick question, when using filter(between(Date, target - 30, target)) does that do target - 30 by records/rows or does it do it by calendar dates. Ideally I would like, to set an arbitrary cutoff even if there are no values. So I would want 30 calendar days from 02-15-2019 for example. – KevS Sep 23 '19 at 23:16
  • @KevS it does it by calendar dates – slava-kohut Sep 23 '19 at 23:21
0

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

Sekhar
  • 61
  • 3