topic Re: Rolling Sum + Max Condition in Dashboards & Analytics Discussions

Rolling Sum + Max Condition

nikhil_cr — Thu, 11 Jul 2024 06:40:19 GMT

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

Re: Rolling Sum + Max Condition

JoeM — Fri, 12 Jul 2024 18:23:38 GMT

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)

Re: Rolling Sum + Max Condition

Spd_0303 — Mon, 15 Jul 2024 09:50:43 GMT

Hi @nikhil_cr,

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

Re: Rolling Sum + Max Condition

nikhil_cr — Mon, 15 Jul 2024 11:04:21 GMT

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

Re: Rolling Sum + Max Condition

JoeM — Mon, 15 Jul 2024 17:50:50 GMT

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

Re: Rolling Sum + Max Condition

nikhil_cr — Wed, 17 Jul 2024 07:03:07 GMT

@JoeM : My bad, Its working. I made some mistake while reading the columns.

FYI - one of our teammates found a solution using SQL - You can refer attached sql code.