12 month comparison in R

Question

I want to calculate a trend based on a 12 month rolling comparison. Compare Janaury 2020 with January 2021 for Category VW

For now my dataset seems like this:

Dates	Category	Sales
2020-01-20	BMW	150
2020-01-20	VW	200
2020-02-20	BMW	250
2020-02-20	VW	300
2020-03-20	BMW	220
2020-03-20	VW	250
..........	.....
2021-01-20	BMW	500
2021-01-20	VW	600
2021-02-20	BMW	200

What i did for now is importing the table and formatting the date right. I am not sure how to calculate the trend. I saw some functions like lag which could be useful but have no concrete plan how to implement it right. Do you have any suggestions?

Do you want to estimate a trend based on the last 12 months, or do you want to estimate a trend from two months separated by 12 months? — user2974951
I want basically for now a trend from the comparison of January 2021 with January 2020. But if you have any solutions for the calc of the trend based on the last 12 months, it could be also helpful. — Udo Gniß

user2974951 user2974951 · Accepted Answer · 2021-10-26T09:24:10

Assuming there is only 1 row per date and category, and assuming all dates have the same day (say 20), then a merge could solve this

df=read.table(text="
Dates   Category    Sales
2020-01-20  BMW 150
2020-01-20  VW  200
2020-02-20  BMW 250
2020-02-20  VW  300
2020-03-20  BMW 220
2020-03-20  VW  250
2021-01-20  BMW 500
2021-01-20  VW  600
2021-02-20  BMW 200",h=T)

library(lubridate)

df$Dates=as_datetime(df$Dates)

df2=df
df2$Dates=df$Dates-months(12)

merge(df,df2,by=c("Dates","Category"),all.x=T,suffixes=c("","_lst"))

       Dates Category Sales Sales_lst
1 2020-01-20      BMW   150       500
2 2020-01-20       VW   200       600
3 2020-02-20      BMW   250       200
4 2020-02-20       VW   300        NA
5 2020-03-20      BMW   220        NA
6 2020-03-20       VW   250        NA
7 2021-01-20      BMW   500        NA
8 2021-01-20       VW   600        NA
9 2021-02-20      BMW   200        NA

where Sales_lst contains the 12 months earlier value, from which you can calculate your desired trend.

12 month comparison in R

1 Answers