07-10-2024 11:36 PM - edited 07-10-2024 11:40 PM
Hi,
I'm trying to achieve a rolling sum with a max condition. Basically trying to do a rolling sum but before we roll the 1st row sum to the next row we have to check with a condition if the rolling value is less than or equal to Zero. If it is less than zero we have to make it as zero else we will roll the sum with the +ve value. Please see attached sample. Column in Green is something which we have to achieve.
Note: Please see comment in cell P11. Please provide a solution on the MV side as the calculation will be further used in other tables.
Appreciate your help
Thanks,
Nikhil
Solved! Go to Solution.
07-12-2024 11:23 AM
Hi @nikhil_cr - try the following:
import pandas as pd
df = read(schema.table)
df['Stock on Day With Max Rollingv2'] = df.apply(
lambda row: max(0, row['Initial Stock Final'] + row['Outstanding Quantity'] - row['Forecast']),
axis=1
)
print(df)
07-15-2024 02:50 AM
Hi @nikhil_cr,
Can you clarify if these both columns are interdependent on each other for calculation ?
07-15-2024 04:01 AM - edited 07-15-2024 04:04 AM
@JoeM - Thanks for the reply, But your code is not giving me the rolling sum.
@Spd_0303 - Yes they are dependent, except the 1st row. Once the stock on day for the 1st day is calculated, it will be our Initial Stock for the next day. see snip -
Stock on Day = Initial Stock + Outstanding Quantity - Forecast
Please note Initial Stock for 2nd day would be the previous stock on day.
07-15-2024 10:50 AM
Value for value, I'm showing the same as you provided. Are you trying to replication something other than "Stock on Day with Max Rolling"?