Hello!
Sometime back I had a query where I was finding it difficult to create list of subsets multiplying with constant values per row and folks were able to help with this query. Thanks again! Here is the link for this topic
Now I am interested in the same thing, but would like to have results for Year and Month instead of just Year and again finding it difficult. I still need subsets for several different years. For solving this problem here, just would like to use 2 subsets 2021 & 2020 to keep it simple. Within each subset, I should be able to have an added variable that can show the sum of last 3 years for any given month. For example, For Feb 2021, we should be able to see sum of last 3 years which would be sum of values from Feb 2021 - March 2018 which would then be multiplied with percentages from perc table in its same sequence from 80% to 55% for each year such as 80 would be used for 2021 year, 40 would be multiplied for 2020 results and 55 would be multiplied with 2018 results. Similarly, for May 2021, sum of values from May 2021 - June 2018 & so on. The same applies for subset with year 2020 as well. I do have several years that need to be in individual subsets. These 3 years sum would then be multiplied by constant values from percentage table just like in the above in its same sequence from 80% to 55% for each year.
In this problem, everything is exactly same, except we would like to perform the same thing while considering each month in a given year.
Help to solve this problem would be highly appreciated.
Thank you in advance!
Adding the sample dataset here with previously solved by folks here for years only & now looking for similar concept for year & month:
library(tidyverse)
library(lubridate)
vol <- data.frame(
Date = c("2018 Jan","2018 Jan","2018 Jan","2018 Jan",
"2018 Feb","2018 Feb","2018 Feb","2018 Feb",
"2018 Mar","2018 Mar","2018 Mar","2018 Mar",
"2018 Apr","2018 Apr","2018 Apr","2018 Apr",
"2018 May","2018 May","2018 May","2018 May",
"2018 Jun","2018 Jun","2018 Jun","2018 Jun",
"2018 Jul","2018 Jul","2018 Jul","2018 Jul",
"2018 Aug","2018 Aug","2018 Aug","2018 Aug",
"2018 Sep","2018 Sep","2018 Sep","2018 Sep",
"2018 Oct","2018 Oct","2018 Oct","2018 Oct",
"2018 Nov","2018 Nov","2018 Nov","2018 Nov",
"2018 Dec","2018 Dec","2018 Dec","2018 Dec",
"2019 Jan","2019 Jan","2019 Jan","2019 Jan",
"2019 Feb","2019 Feb","2019 Feb","2019 Feb",
"2019 Mar","2019 Mar","2019 Mar","2019 Mar",
"2019 Apr","2019 Apr","2019 Apr","2019 Apr",
"2019 May","2019 May","2019 May","2019 May",
"2019 Jun","2019 Jun","2019 Jun","2019 Jun",
"2019 Jul","2019 Jul","2019 Jul","2019 Jul",
"2019 Aug","2019 Aug","2019 Aug","2019 Aug",
"2019 Sep","2019 Sep","2019 Sep","2019 Sep",
"2019 Oct","2019 Oct","2019 Oct","2019 Oct",
"2019 Nov","2019 Nov","2019 Nov","2019 Nov",
"2019 Dec","2019 Dec","2019 Dec","2019 Dec",
"2020 Jan","2020 Jan","2020 Jan","2020 Jan",
"2020 Feb","2020 Feb","2020 Feb","2020 Feb",
"2020 Mar","2020 Mar","2020 Mar","2020 Mar",
"2020 Apr","2020 Apr","2020 Apr","2020 Apr",
"2020 May","2020 May","2020 May","2020 May",
"2020 Jun","2020 Jun","2020 Jun","2020 Jun",
"2020 Jul","2020 Jul","2020 Jul","2020 Jul",
"2020 Aug","2020 Aug","2020 Aug","2020 Aug",
"2020 Sep","2020 Sep","2020 Sep","2020 Sep",
"2020 Oct","2020 Oct","2020 Oct","2020 Oct",
"2020 Nov","2020 Nov","2020 Nov","2020 Nov",
"2020 Dec", "2020 Dec","2020 Dec", "2020 Dec",
"2021 Jan","2021 Jan","2021 Jan","2021 Jan",
"2021 Feb","2021 Feb","2021 Feb","2021 Feb",
"2021 Mar","2021 Mar","2021 Mar","2021 Mar",
"2021 Apr","2021 Apr","2021 Apr","2021 Apr",
"2021 May","2021 May","2021 May","2021 May",
"2021 Jun","2021 Jun","2021 Jun","2021 Jun",
"2021 Jul","2021 Jul","2021 Jul","2021 Jul",
"2021 Aug","2021 Aug","2021 Aug","2021 Aug",
"2021 Sep","2021 Sep","2021 Sep","2021 Sep",
"2021 Oct","2021 Oct","2021 Oct","2021 Oct",
"2021 Nov","2021 Nov","2021 Nov","2021 Nov",
"2021 Dec","2021 Dec", "2021 Dec","2021 Dec"),
Country = c("CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US",
"CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US",
"CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US",
"CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US","CA","CA","US","US"),
Type = c("A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B",
"A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B",
"A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B",
"A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B", "A", "B"
),
Sales = c(100,110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210,100,110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210,100,110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210,100,110, 120, 130, 140, 150, 160, 170, 180, 190, 200, 210,
220, 230,240, 250, 260, 270, 280, 290, 300, 310, 320, 330,220, 230,240, 250, 260, 270, 280, 290, 300, 310, 320, 330,20, 230,240, 250, 260, 270, 280, 290, 300, 310, 320, 330,220, 230,240, 250, 260, 270, 280, 290, 300, 310, 320, 330,
340, 350, 360, 370, 380, 390, 400, 410, 420, 430, 440, 450,340, 350, 360, 370, 380, 390, 400, 410, 420, 430, 440, 450,340, 350, 360, 370, 380, 390, 400, 410, 420, 430, 440, 450,340, 350, 360, 370, 380, 390, 400, 410, 420, 430, 440, 450,
460, 470, 480, 490, 500, 510, 520, 530, 540, 550, 560, 570, 460, 470, 480, 490, 500, 510, 520, 530, 540, 550, 560, 570, 60, 470, 480, 490, 500, 510, 520, 530, 540, 550, 560, 570, 460, 470, 480, 490, 500, 510, 520, 530, 540, 550, 560, 570)
)
vol$Date <- ym(vol$Date)
vol$year <- year(vol$Date)
perc <- data.frame(
y = 1:3,
Percentage = c(80, 40, 55)
)
# Below is where we would need subsets of years with month in a way that it works easily for several years as subsets as I have at least 8-10 years in real data.
time_periods_of_interest <- c(
2021,
2020
)
evaluations <- map(time_periods_of_interest,
~ seq(to = ., by = 1, length.out = 3)
) %>% set_names(time_periods_of_interest)
(step_1 <- map(
evaluations,
~ {
filter(
vol,
year %in% .x
) %>%
group_by(Country,Type,year) %>%
summarise(sumsales = sum(Sales)) %>%
ungroup() %>%
mutate(y=dense_rank(desc(year))) %>% left_join(perc) })
)
(step_2 <- map(step_1, ~ {
mutate(.x, per_sum_sales = sumsales * Percentage / 100)
}))