I'd like to modify the cumsum functions. I would like negative values to be changed into 0. And also when there is a new D value different from 0 then use D-S.
Below example:
TD <- data.frame(product = rep("A", 7),
data = seq(as.Date("2020-01-01"), as.Date("2020-01-07"), by = "day"),
D = c(74, 0, 0, 0, 0, 20, 10), S = c(20, 30, 20, 5, 2, 4, 5))
TD <- TD %>% group_by(product) %>% mutate(result = cumsum(D) - cumsum(S))
> TD
# A tibble: 6 x 5
# Groups: product [1]
product data D S result I expected
<chr> <date> <dbl> <dbl> <dbl>
1 A 2020-01-01 74 20 54 54
2 A 2020-01-02 0 30 24 24
3 A 2020-01-03 0 20 4 4
4 A 2020-01-04 0 5 -1 0
5 A 2020-01-05 0 2 -3 0
6 A 2020-01-06 20 4 13 16
7 A 2020-01-07 10 5 18 21
I think this function does what you want
pos_cumsum <- function(x) {
cs <- cumsum(x)
cm <- cummin(cs)
return (cs - pmin(cm, 0))
}
TD<- TD%>% group_by(product) %>% mutate(result = pos_cumsum(D-S))
TD
#> # A tibble: 7 x 5
#> # Groups: product [1]
#> product data D S result
#> <chr> <date> <dbl> <dbl> <dbl>
#> 1 A 2020-01-01 74 20 54
#> 2 A 2020-01-02 0 30 24
#> 3 A 2020-01-03 0 20 4
#> 4 A 2020-01-04 0 5 0
#> 5 A 2020-01-05 0 2 0
#> 6 A 2020-01-06 20 4 16
#> 7 A 2020-01-07 10 5 21
Although I'd wonder about whether the D happens before or after the S...