# ETL
library(tidyverse)
# Charts
library(highcharter)
# Supply Chain
library(planr)30 Conversion Monthly to Weekly bucket
Let’s upload the libraries we’re going to use :
We’re going to present here how to use the month_to_week() function from the R package planr.
This function allows to split a Demand (for ex.: Sales Forecasts) from monthly into weekly buckets.
By default, the split is performed evenly for each week.
We will see later another function, called month_to_weekx() which allows to define a particular weekly pattern.
We need a data frame with the 3 variables to use this function :
a Product: it’s an item, a SKU (Storage Keeping Unit), or a SKU at a location, also called a DFU (Demand Forecast Unit).
a Period of time : here in monthly buckets only.
a Demand : could be some sales forecasts, expressed in units.
Get demo data frame
Let’s use the built-in demo data frame demo_monthly_dmd from the planr package.
It’s a data frame with :
2 products (Product A & Product B).
a Period expressed in monthly bucket.
and the related monthly Demand.
# get dataset
demo_monthly_dmd <- planr::demo_monthly_dmd
head(demo_monthly_dmd) DFU Period Demand
1 Product A 2023-01-01 1000
2 Product A 2023-02-01 1000
3 Product A 2023-03-01 2000
4 Product A 2023-04-01 1000
5 Product A 2023-05-01 1000
6 Product A 2023-06-01 2000Let’s look at the Product A, displaying the monthly Demand (Sales Forecasts).
In the next part, we will split this monthly Demand into weekly bucket.
# set a working df
df1 <- demo_monthly_dmd
# Select only the Product A
df1 <- filter(df1, df1$DFU == "Product A")
#-----------------
# chart
#-----------------
highchart() |>
hc_title(text = "Monthly Demand") |>
hc_subtitle(text = "in units") |>
hc_add_theme(hc_theme_google()) |>
hc_xAxis(categories = df1$Period) |>
hc_add_series(name = "Initial Demand",
color = "mediumseagreen",
dataLabels = list(align = "center", enabled = TRUE),
data = df1$Demand) |>
hc_chart(type = "column") 31 Convert Monthly Demand into Weekly Demand
31.1 Split into weekly bucket
Let’s apply the function, month_to_week() on the data frame demo_monthly_dmd.
The previous period was initially expressed in monthly bucket and will be expressed as weekly bucket after applying the function.
The monthly demand will also be split into weekly demand.
By default, the split is performed evenly for each week, as displayed in the next chart.
# apply month_to_week()
Weekly_Demand <- planr::month_to_week(dataset = demo_monthly_dmd,
DFU = DFU,
Period = Period,
Demand = Demand)
head(Weekly_Demand) DFU Period Demand
1 Product A 2023-01-01 250.0000
2 Product A 2023-01-08 250.0000
3 Product A 2023-01-15 250.0000
4 Product A 2023-01-22 250.0000
5 Product A 2023-01-29 142.8571
6 Product A 2023-02-05 250.000031.2 Chart Weekly Demand
Let’s look at the Product A and display the new calculated weekly Demand.
# set a working df
df1 <- Weekly_Demand
# Select only the Product A
df1 <- filter(df1, df1$DFU == "Product A")
#-----------------
# chart
#-----------------
highchart() |>
hc_title(text = "Calculated Weekly Demand") |>
hc_subtitle(text = "in units") |>
hc_add_theme(hc_theme_google()) |>
hc_xAxis(categories = df1$Period) |>
hc_add_series(name = "Weekly Demand",
color = "gold",
#dataLabels = list(align = "center", enabled = TRUE),
data = df1$Demand) |>
hc_chart(type = "column") The Monthly Demand has been split evenly into weekly bucket.
Now, let’s analyze the results to better understand how it works.
32 Analyze Results
32.1 Summary table
We want to analyze the quality of the split of the Demand, from monthly into weekly buckets.
Let’s then aggregate the calculated weekly Demand into monthly bucket, and compare :
the monthly values with the initial ones.
the accumulated monthly values.
We will look at the Product A for our analysis.
# set a working dataset
df1 <- Weekly_Demand
# create a (Monthly) Period
df1$Monthly.Period <- floor_date(df1$Period, unit = "month")
# aggregate
df1 <- df1 |> group_by(DFU, Monthly.Period) |>
summarise(New.Demand = sum(Demand)
)
# rename
df1 <- df1 |> rename(Period = Monthly.Period)
#-----------------------------
# Add to initial dataset
#-----------------------------
# merge
df1 <- left_join(demo_monthly_dmd, df1)
# calculate accumulated values
df1 <- df1 |> group_by(DFU, Period) |>
summarise(
Demand = sum(Demand),
New.Demand = sum(New.Demand)
) |>
mutate(
acc_Demand = cumsum(Demand),
acc_New.Demand = cumsum(New.Demand)
)
# formatting
df1 <- as.data.frame(df1)
# calculate delta
df1$delta <- (df1$acc_New.Demand - df1$acc_Demand) / df1$acc_Demand
#-----------------
# Focus on Product A
#-----------------
# Select only the Product A
df1 <- filter(df1, df1$DFU == "Product A")
#-----------------
# Formatting for a better display
#-----------------
df1$New.Demand <- as.integer(df1$New.Demand)
df1$acc_New.Demand <- as.integer(df1$acc_New.Demand)
df1 DFU Period Demand New.Demand acc_Demand acc_New.Demand delta
1 Product A 2023-01-01 1000 1142 1000 1142 0.142857143
2 Product A 2023-02-01 1000 1142 2000 2285 0.142857143
3 Product A 2023-03-01 2000 1750 4000 4035 0.008928571
4 Product A 2023-04-01 1000 1178 5000 5214 0.042857143
5 Product A 2023-05-01 1000 1000 6000 6214 0.035714286
6 Product A 2023-06-01 2000 1821 8000 8035 0.004464286
7 Product A 2023-07-01 1000 1142 9000 9178 0.019841270
8 Product A 2023-08-01 1000 964 10000 10142 0.014285714
9 Product A 2023-09-01 2000 1857 12000 12000 0.000000000
10 Product A 2023-10-01 1000 1142 13000 13142 0.010989011
11 Product A 2023-11-01 1000 1000 14000 14142 0.010204082
12 Product A 2023-12-01 2000 1857 16000 16000 0.000000000We can see that there is a little difference between the initial and the new value.
However, how is it when we look at the accumulated values ?
We can see that the accumulated values show almost no difference.
during the 1st two periods there is a slight difference.
which almost totally disappears from the 3rd period.
Let’s look at it through 2 charts :
32.2 Chart 1 : Initial vs New Demand
Let’s compare the initial & the new monthly Demand :
#-----------------
# chart
#-----------------
highchart() |>
hc_title(text = "Initial vs New Demand") |>
hc_subtitle(text = "in units") |>
hc_add_theme(hc_theme_google()) |>
hc_xAxis(categories = df1$Period) |>
hc_add_series(name = "Initial Demand",
color = "mediumseagreen",
dataLabels = list(align = "center", enabled = TRUE),
data = df1$Demand) |>
hc_add_series(name = "New Demand",
color = "gold",
dataLabels = list(align = "center", enabled = TRUE),
data = df1$New.Demand) |>
hc_chart(type = "column") Depending on the months, the New Demand (i.e. aggregation of the weekly demand) is sometimes higher or lower than the Initial Demand.
When we look at it from an accumulated perspective, the 2 values are overall very aligned, as displayed in the next chart.
32.3 Chart 2 : Accumulated Initial vs New Demand
highchart() |>
hc_title(text = "Accumulated Initial vs New Demand") |>
hc_subtitle(text = "in units") |>
hc_add_theme(hc_theme_google()) |>
hc_xAxis(categories = df1$Period) |>
hc_add_series(name = "Initial Demand",
color = "mediumseagreen",
dataLabels = list(align = "center", enabled = TRUE),
data = df1$acc_Demand) |>
hc_add_series(name = "New Demand",
color = "gold",
dataLabels = list(align = "center", enabled = TRUE),
data = df1$acc_New.Demand) |>
hc_add_theme(hc_theme_google())