Rolling Sum + Max Condition

[nikhil_cr]

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

[JoeM]

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)

[Spd_0303]

Hi @nikhil_cr,

Can you clarify if these both columns are interdependent on each other for calculation ?

[nikhil_cr]

@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. 

[JoeM]

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"?