基于销售数据按周统计库存数量的R语言实现问题

需求说明 #

• 按周聚合数据，统计每周库存商品总数，需覆盖全新及二手商品
• 已知核心数据：销售日期（Contract Date）、商品入库日期、未售出库存商品列表

对应Excel公式逻辑 #

需实现的Excel统计公式为：

=COUNTIFS(New!$A$2:$A$43254,">="&A3,New!$O$2:$O$43254,"<"&WeeklyData!A2)+COUNTIF(NewInventory!$M$2:$M$939,"<"&A3)

各表字段对应关系：

• New表：A列=销售日期（Contract Date），O列=售出商品入库日期
• NewInventory表：N列=未售出商品入库日期
• WeeklyData表：A列=每周首日（格式：月/日/年）

已完成的R代码步骤 #

1. 新增周/年标识列（格式如52/2022）：

WeeklyVariableData$New$WeekYearSale <- strftime(WeeklyVariableData$New$'Contract Date', format = "%U/%Y")
WeeklyVariableData$New$WeekYearInv.x <- strftime(WeeklyVariableData$New$'DateAdded', format = "%U/%Y")

2. 通过唯一标识Key关联已售和库存商品全量数据：

NewWeeklyData <-
  full_join(WeeklyVariableData$New,
            WeeklyVariableData$NewInventory %>% dplyr::select(Key, DateAdded, WeekYearInv.y), 
            by = "Key")
NewWeeklyData

当前问题 #

按周/年分组统计销售日期≥该周且入库日期≤该周的商品数量时，结果远低于预期，使用的统计代码如下：

NCInv <- NewWeeklyData %>%
  group_by(WeekYearSale) %>%
  summarize(NCInvSold = sum(WeekYearSale >= WeekYearInv.x, na.rm = TRUE))

NCInv

后续需统计每周未售出库存商品数量，合并数据求和得到每周总库存，但因前期统计结果异常，导致最终库存数据错误。

数据样例（前3行） #

structure(list(`Contract Date` = structure(c(1577836800, 1577836800, 
1577836800), class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
    ModelYear = c(2020, 2020, 2019), Make = c("Honda", "Subaru", 
    "Volkswagen"), Key = c("16H6419*5FNYF6H58LB019602", "15SD7429*4S4BTALCXL3156024", 
    "15VD4787*3VWC57BU3KM253219"), DateAdded = structure(c(18179, 
    18262, 18176), class = "Date"), WeekYearSale = c("52/2019", 
    "52/2019", "52/2019"), WeekYearInv.x = c("40/2019", "00/2020", 
    "40/2019")), row.names = c(NA, 3L), class = "data.frame")

内容的提问来源于stack exchange，提问作者talkingtoducks