This article describes how to use R code with a Date/Time variable to create dynamic custom variables which can be used as filters.
A Date/Time variable. In this example, we use a date variable called StartDate. Data in R is in the format YYYY/MM/DD:
In general, the steps for creating binary R variables are as follows:
1. Hover over any variable in the Data Sets tree > Plus (+) > Custom Code > R - Numeric or in the toolbar select Anything > Data > Variables > New > Custom Code > R - Numeric.
2. OPTIONAL: Tick Properties > GENERAL > Usable as a filter.
3. Paste your code under Properties > R CODE.
Below are some examples of the types of date-based conditions that you can easily create.
1. Before/After Date
StartDate is before October 2020:
StartDate < "2020/10/01"
Here, the date format must match YYYY/MM/DD.
2. Date Range
StartDate is between October and December 2020:
StartDate >= "2020/10/01" & StartDate <= "2020/12/31"
Again, the date format must match YYYY/MM/DD.
3. Last/Next K period
StartDate is within the last 3 months:
StartDate >= Sys.Date() - months(3)
- Here, the months argument is from the lubridate package so we need to reference this library.
- This can be changed to days, weeks or years, and you can change the – to a + for future periods.
- Sys.Date() is simply the system date or the current date on the server.
4. Previous/Current/Next period
StartDate falls within the current quarter:
Period(StartDate, "quarter") == Period(Sys.Date(), "quarter")
- Here, the Period argument is from our flipTime package so we need to reference this library.
- The quarter as defined by StartDate is then compared to the current quarter based on today's date.
- The quarter argument can be changed to day, week, month or year.
- You can also add the period arguments from the previous example to adjust when the period falls. For example, comparing StartDate to next quarter is as simple as adding 3 months to today's date:
Period(StartDate, "quarter") == Period(Sys.Date() + months(3), "quarter")
5. Date Filtering
The 2. Date Range code above can be further expanded on to use Date Controls (see How to Insert a Date Control). Where your Start Date control is named
Date and your End Date control is named
Date.2, you can use the following code in a new R filter variable (similar to How to Connect Filters to Controls Using R Code) to select responses that have a StartDate during that range:
DateFilter = StartDate >= Date & StartDate <= Date.2
Below is an example of the raw StartDate and DateFilter data to show how the dates will be filtered in (the 1s in the DateFilter column) based on the control boxes above:
You would then use the DateFilter as a filter on your tables and outputs that you'd like to filter.