30  Conversion Monthly to Weekly bucket

Let’s upload the libraries we’re going to use :

# ETL
library(tidyverse)

# Charts
library(highcharter)

# Supply Chain
library(planr)

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 :

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 :

# 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   2000

Let’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.0000

31.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.000000000

We 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())