How to Create a Date Filter for Last X Months

From Q
Jump to: navigation, search

When working with date questions, it is often useful to have filters that store the most recent period vs. either the rest of the data or for comparison against the preceding period.

To create a variable that stores the last 3 months (current month included), take these steps:

1. Go to Insert > R (Variables) > Numeric Variable
2. Under Properties > R CODE paste in the code below, replacing "datevar" on the first line with the name of your date variable.

data <- datevar
library(lubridate)
library(zoo)
last3mon <- as.Date(as.yearmon(Sys.Date()), frac = 0) %m-% months(2)
data >= last3mon

3. Under Inputs > Structure change the drop-down to Nominal: Mutually exclusive categories
4. Optional: to make this a filter check the box GENERAL > Usable as a filter

To create a variable that stores the preceding 3 months (i.e. before the most recent 3 months), take the same steps as above, but use the code here:

data <- datevar
library(lubridate)
library(zoo)
last3mon <- as.Date(as.yearmon(Sys.Date()), frac = 0) %m-% months(2)
last6mon <- as.Date(as.yearmon(Sys.Date()), frac = 0) %m-% months(5)
data < last3mon & data >= last6mon