12  Get Dates Attributes

We’re going to create a kind of generic and reusable code to create a data frame with a complete dates features :

We’re going to practice on :

# ETL
library(tidyverse)

13 Create a date sequence

Let’s generate a calendar period of time. We’ll define the duration as 36 months, with the following start and end dates:

  • Starting from July 2021.

  • Ending in June 2024.

We use the function seq.Date() to generate a sequence of date, defining the interval “by month”. It allows us to generate 36 periods of times, in monthly bucket, between the 2 previous start and end dates.

# Define the start and end dates
start_date <- as.Date("2021-07-01")
end_date <- as.Date("2024-06-01")

# Generate a sequence of dates from start_date to end_date with monthly intervals
period <- seq.Date(from = start_date, to = end_date, by = "month")

# formatting
calendar_period_data <- as.data.frame(period)

# Display the date sequence
head(calendar_period_data)
      period
1 2021-07-01
2 2021-08-01
3 2021-09-01
4 2021-10-01
5 2021-11-01
6 2021-12-01

14 Add date attributes

Now, we are going to :

  • extract some attributes related to this calendar period of time.

    • calendar month.

    • calendar year.

    • calendar month abbreviation.

      • we will transform it into a factor, to align with the fiscal period of time.

      • in this example, the fiscal period starts in July and ends in June.

        • the first month (of the fiscal year) is then July, and the last one is June.

  • generate some attributes related to the fiscal period of time.

    • a fiscal year : based on the variable period, using the function if_else().

    • a fiscal month : creating and merging a small data frame Fiscal_Calendar_data.

# set a working df
df1 <- calendar_period_data

#-----------------------
# Add Calendar date attributes
#-----------------------

# Get calendar_month
df1$calendar_month <- month(df1$period)

# Get calendar_year
df1$calendar_year <- year(df1$period)


# Add calendar_month_abb
df1$calendar_month_abb <- month.abb[df1$calendar_month]


# create a factor
df1$calendar_month_abb <- factor(df1$calendar_month_abb,                                 
                                 levels = c("Jul","Aug","Sep", "Oct", "Nov", "Dec",
                                            "Jan","Feb","Mar","Apr","May","Jun"))



#-----------------------
# Add fiscal calendar attributes
#-----------------------


df1$fiscal_year <- if_else(df1$period >= "2021-07-01" & df1$period <= "2022-06-01",
                           "FY22", "TBC")

df1$fiscal_year <- if_else(df1$period >= "2022-07-01" & df1$period <= "2023-06-01",
                           "FY23", df1$fiscal_year)

df1$fiscal_year <- if_else(df1$period >= "2023-07-01" & df1$period <= "2024-06-01",
                           "FY24", df1$fiscal_year)

df1$fiscal_year <- if_else(df1$period >= "2024-07-01" & df1$period <= "2025-06-01",
                           "FY25", df1$fiscal_year)




#----------------------
# Add fiscal_month
# Create Fiscal_Calendar_DB
#----------------------


calendar_month <- c(7:12, 1:6)
fiscal_month <- c(1:12)

# Create a Fiscal Calendar
Fiscal_Calendar_data <- data.frame(calendar_month,
                                    fiscal_month)



#----------------------
# Merge
#----------------------

df1 <- left_join(df1, Fiscal_Calendar_data)




glimpse(df1)
Rows: 36
Columns: 6
$ period             <date> 2021-07-01, 2021-08-01, 2021-09-01, 2021-10-01, 20…
$ calendar_month     <dbl> 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,…
$ calendar_year      <dbl> 2021, 2021, 2021, 2021, 2021, 2021, 2022, 2022, 202…
$ calendar_month_abb <fct> Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, M…
$ fiscal_year        <chr> "FY22", "FY22", "FY22", "FY22", "FY22", "FY22", "FY…
$ fiscal_month       <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3, 4, …

We have here all the attributes to :

  • create some YTD vs LYTD analysis, based on Fiscal Years.

  • create some classic charts : Month to Month (MTM), Year to Date (YTD).

Let’s display the first 6 rows of the results, using the function head() .

head(df1)
      period calendar_month calendar_year calendar_month_abb fiscal_year
1 2021-07-01              7          2021                Jul        FY22
2 2021-08-01              8          2021                Aug        FY22
3 2021-09-01              9          2021                Sep        FY22
4 2021-10-01             10          2021                Oct        FY22
5 2021-11-01             11          2021                Nov        FY22
6 2021-12-01             12          2021                Dec        FY22
  fiscal_month
1            1
2            2
3            3
4            4
5            5
6            6