How to Create a Weekly Moving Average Variable Using R

This article describes how to go from a Date/Time variable showing weekly data:

To a binary-multi variable set showing 10 weekly moving (rolled) averages:

Requirements

• A Date/Time variable. Date/Time variables have a Calendar icon next to them in the Data Sources tree. 
  See "Interview date" below:
  

Method

1. In the Data Sources tree, hover over any variable and click + > Custom Code > R > Numeric - Multi.
2. Input the number of variables (including placeholders for future waves) and click OK. 
   In this example, we input 52 to create a year's worth of weekly averages
   

3. Copy the R code below, updating the date/time variable name (e.g., indate) and specifying minDate and maxDate values.

   # Step 1: Update the code
   # Define Date/Time Variable name
   Date <- intdate
   
   #Define the start & end dates using format YYYY/MM/DD 
   #In this example the start date is 2024/01/01 as per the table above
   #The end data is 52 weeks after; 2025/01/01
   minDate <- "2024/01/01" 
   maxDate <- "2025/01/01" 
   
   # Step 2: Generate weekly columns (week commencing = Monday) 
   weeks <- seq(as.Date(minDate), as.Date(maxDate), by = "week") 
   
   # Step 3: Build 10-week rolling indicator 
   df_matrix <- sapply(weeks, function(week_start) { 
     window_start <- week_start - weeks(9)  # 10 weeks beginning at the end of this week (inclusive) 
     as.integer(Date >= window_start & Date <= week_start + days(6)) 
   }) 
   
   # Step 4: Format column names as "D Mon YYYY" 
   formatted_colnames <- format(weeks, "%-d %b %Y") 
   
   # Step 5: Final dataframe 
   df_result <- as.data.frame(df_matrix) 
   colnames(df_result) <- formatted_colnames 
   df_result

   Note: the code creates 10 weekly rolling averages. You can change the number of weeks by updating the number of weeks in Step 3 of the R code (e.g., week_start - weeks(9)). 

4. Select Variable 1 in the newly created "New R Variable Set" in the Data Sources tree and go to Data > R Code - Data > Edit Code in the object inspector .
5. Paste the code from Step 3 above into the Code Editor , starting from line 18:
   
6. Click Calculate in the object inspector .
7. Select the "New R Variable Set" and go to Data > Properties > Structure in the object inspector and select Binary-Multi: Non- mutually exclusive categories:
8. Right-click the variable set in the Data Sources tree and select Rename to update the name.
9. OPTIONAL: Drag and drop the newly created variable to the Report tree or onto a page to create a table:
   
10. OPTIONAL: Select the Date/Time variable from Data > Data Sources > Columns in the object inspector and check the data.
    

Next

How to Create a Rolling Average on Tables and Visualizations