这是我的数据的样子。最右边的列是我的Desired column。
Name EventType EventDate SalesAmount RunningTotal Runningtotal(prior365Days)
John Email 1/1/2014 0 0 0
John Sale 2/1/2014 10 10 10
John Sale 7/1/2014 20 30 30
John Sale 4/1/2015 30 60 50
John Webinar 5/1/2015 0 60 50
Tom Email 1/1/2014 0 0 0
Tom Sale 2/1/2014 15 15 15
Tom Sale 7/1/2014 10 25 25
Tom Sale 4/1/2015 25 50 35
Tom Webinar 5/1/2015 0 50 35
我只是想在过去365天的窗口中获得每个名称的SalesAmount的运行总数。对于一般的"RunningTotal"列,我使用:
df<- df%>%
group_by (Name)%>%
mutate(RunningTotal = cumsum(SalesAmount))
但是我不知道如何获得过去365天窗口内的运行总数。请帮助。衷心感谢您的帮助!
df$EventDate <- as.Date(df$EventDate, format="%d/%m/%Y")
df <- df %>%
group_by (Name) %>%
arrange(EventDate) %>%
mutate(day = EventDate - EventDate[1])
f <- Vectorize(function(i)
sum(df[df$Name[i] == df$Name & df$day[i] - df$day >= 0 &
df$day[i] - df$day <= 365, "SalesAmount"]), vec="i")
df$RunningTotal365 <- f(1:nrow(df))