-1

I am trying to generate a series by summing the next 3 values and next 4 values to calculate the forcast_leadtime and forecast weeks of supply in R. in the first row i have populated the expected values for both which is 1.008 & 1.64 respectively. Could you advise how to generate this running series in R. Thank you

forecast_leadtime(FL) = Sum of SYS_FORC for Weeks 2,3 4 forecast_weeks of supply(FWOS) = Sum of SYS_FORC for Weeks 5,6,7,8

SKU STORE_CD FWK_CD WK_Sequence_NBR SYS_FORC FL FWOS 12345 10 201648 935 0.328 1.008 1.64 12345 10 201649 936 0.326 0 0 12345 10 201650 937 0.323 0 0 12345 10 201651 938 0.359 0 0 12345 10 201652 939 0.366 0 0 12345 10 201701 940 0.414 0 0 12345 10 201702 941 0.433 0 0 12345 10 201703 942 0.4enter image description here27 0 0 12345 10 201704 943 0.421 0 0 12345 10 201705 944 0.422 0 0

S2850951
  • 182
  • 8
  • 1
    What did you try? Why did it not work? – Heroka Apr 29 '17 at 10:28
  • Try with `roll_sum` from `RcppRoll` i.e. `c(roll_sum(df1$SYS_FORC, 3, fill = NA, align = "left")[-1], NA)` and for the second column, change 3 to 4 – akrun Apr 29 '17 at 10:31
  • Thanks for the response. I am new to R. I have added an image with all the columns and populated the expected values in the first row. Greatly appreciate your help with this. i tried this but unfortunately it didn't work calc_need = calc_need %>% mutate(forecast_lt = c(roll_sum(calc_need$SYS_FORC, 3, fill = NA, align = "left")[-1], NA)) %>% mutate(forecast_wos = c(roll_sum(calc_need$SYS_FORC, 4, fill = NA, align = "left")[-1], NA)) – S2850951 Apr 29 '17 at 10:42

1 Answers1

0

Following @akrun approach but using library zoo and dplyr lead. I have grouped by SKU STORE_CD to show that this is possible

df <- read.table(header= TRUE, text=
"SKU STORE_CD    FWK_CD  WK_Sequence_NBR SYS_FORC    forecast_leadtime   forecast_weeksofsupply
12345   10  201648  935 0.328   1.008   1.64
12345   10  201649  936 0.326   0   0
12345   10  201650  937 0.323   0   0
12345   10  201651  938 0.359   0   0
12345   10  201652  939 0.366   0   0
12345   10  201701  940 0.414   0   0
12345   10  201702  941 0.433   0   0
12345   10  201703  942 0.427   0   0
12345   10  201704  943 0.421   0   0
12345   10  201705  944 0.422   0   0
")


library(zoo)
library(dplyr)
df %>% 
  group_by(SKU, STORE_CD) %>%
  mutate(forecast_leadtime = rollsum(lead(SYS_FORC), 3, na.pad = TRUE, align = "left"),
         forecast_weeksofsupply = rollsum(lead(SYS_FORC, 4), 4, na.pad = TRUE,  align = "left"))

#      SKU STORE_CD FWK_CD WK_Sequence_NBR SYS_FORC forecast_leadtime forecast_weeksofsupply
# 1  12345       10 201648             935    0.328             1.008                  1.640
# 2  12345       10 201649             936    0.326             1.048                  1.695
# 3  12345       10 201650             937    0.323             1.139                  1.703
# 4  12345       10 201651             938    0.359             1.213                     NA
# 5  12345       10 201652             939    0.366             1.274                     NA
# 6  12345       10 201701             940    0.414             1.281                     NA
# 7  12345       10 201702             941    0.433             1.270                     NA
# 8  12345       10 201703             942    0.427                NA                     NA
# 9  12345       10 201704             943    0.421                NA                     NA
# 10 12345       10 201705             944    0.422                NA                     NA
Andrew Lavers
  • 4,328
  • 1
  • 12
  • 19