cancel
Showing results for 
Search instead for 
Did you mean: 

Rolling Sum + Max Condition

nikhil_cr
Cosmonaut

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

5 REPLIES 5

JoeM
Community Manager
Community Manager

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
Ranger

Hi @nikhil_cr,

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

Spd_0303_0-1721037021481.png

 

nikhil_cr
Cosmonaut

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

nikhil_cr_0-1721041169543.png

Stock on Day = Initial Stock + Outstanding Quantity - Forecast

Please note Initial Stock for 2nd day would be the previous stock on day. 

 

 

 

JoeM
Community Manager
Community Manager

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

JoeM_0-1721065805930.png