I have a data.table
dt <- data.table(
Date = c("20240701", "20240801", "20240901", "20241001"),
Plan = c(85,17,50, 34),
OpenPlan = c(85, 0,33, 0),
ValuetoReduce = c(97,97,97,97)
)
I want to reduce “Plan” over all periods in total by 97 (ValuetoReduce)
In my mind this looks like:
- Row number 1: I can reduce the “Plan” by 85 (OpenPlan). The NewPlan then is 0. As I consumed 85 I have 12 left which i can reduce in the rows
- Row number 2: Here OpenPlan is 0, so I can’t reduce anything, so set the “NewPlan” as “Plan”.
- Row number 3: Here I can reduce 33 (OpenPlan), but I only have 12 left. So reduce the 50 (Plan) by 12 as NewPlan
- Following rows: Nothing to reduce anymore, so set the NewPlan equal to Plan
New dt should like look like
dt_new <- data.table(
Date = c("20240701", "'20240801", "20240901", "20241001"),
Plan = c(85,17,50, 34),
OpenPlan = c(85, 0,33, 0),
ValuetoReduce = c(97,97,97,97),
NewPlan = c(0,17,38,34)
)
Thanks!