aggregate daily sales to weekly

[jak123]

Hi i have the following df (header)

structure(list(ItemId = c("AS1020", "AS1020", "AS1020", "AS1020",
"AS1020", "AS1020"), SalesQty = c(1, 6, 1, 1, 1, 1), CustAccount = c("14013",
"305009", "17184", "EVET", "10486", "10800"), createdDateTime = structure(c(1577979900,
1577984448, 1578296412, 1578311977, 1578321138, 1578406933), tzone = "UTC", class = c("POSIXct",
"POSIXt"))), row.names = c(NA, -6L), class = c("tbl_df", "tbl",
"data.frame"))

i want date to aggregate the qty sales by week for all the itemIDs

thanks

[GreyMerchant]

Hi there,

Here is an example with your data where we are grouping on each day. You can extract the month from whole_date and then group_by on that and then sum that to get the answer you want (in your example it would not have led to an interesting result). You can also add ItemId on which to group in addition to the date.

df <- structure(list(ItemId = c("AS1020", "AS1020", "AS1020", "AS1020",
                                "AS1020", "AS1020"), SalesQty = c(1, 6, 1, 1, 1, 1), CustAccount = c("14013",
                                                                                                     "305009", "17184", "EVET", "10486", "10800"), createdDateTime = structure(c(1577979900,
                                                                                                                                                                                 1577984448, 1578296412, 1578311977, 1578321138, 1578406933), tzone = "UTC", class = c("POSIXct",
                                                                                                                                                                                                                                                                       "POSIXt"))), row.names = c(NA, -6L), class = c("tbl_df", "tbl","data.frame"))

library(tidyverse)
library(lubridate)

df2 <- df %>% 
  dplyr::mutate(whole_date = lubridate::as_date(df$createdDateTime)) %>% 
  dplyr::group_by(whole_date) %>% 
  dplyr::summarise(sum_whole = sum(SalesQty))



df2
#> # A tibble: 3 x 2
#>   whole_date sum_whole
#>   <date>         <dbl>
#> 1 2020-01-02         7
#> 2 2020-01-06         3
#> 3 2020-01-07         1

^{Created on 2022-02-02 by the reprex package (v2.0.1)}