Cummulative total with restart

[b_bui]

Hello Knime community,
I would need your help to get cummlative total with reset condition. My sample data here:
KNIME_project.knwf (30.3 KB)

I tried to get Supportable qty for each item by month based on thier current Actual stock using running total. However, I see this method is not meet my requirement

Can you guide me how i can get the correct supportable?

[AlexanderFillbrunn]

Hi,
Can you explain a little what the Excel table above means? I assume “Correct Supportable” is what you want. From which columns is it calculated and what is the reset condition?
Kind regards,
Alexander

[b_bui]

Hello Alex,
Yes you right, “Correct supportable” is the outcome i want to archive.

I not sure if cummulative is best option here, but this only solution i could think of.
Supportable:

• If(Project OH > 0 => 100% supportable , return to ( MTG RF to Order + Total DC Demand )
• If(Project OH < 0 => partial supportable , return to (Project OH M-1) + (MTG RF to Order + Total DC Demand))
  Example Hongkong
  In May projected OH is -11676 which mean total supportable only 4964 (from DC OH)
  Begining of Jun they received 8145 (from DC Supply) => Jun supportable = 8145.
  Reset condition is based on DC Supply. If there is supply will consider Supply intead.
  Thank you for helping

[AlexanderFillbrunn]

Hi,
Sorry, I still do not get it. In both May and June for Hongkong the Project OH is < 0 (-11676 and -11723), so why treat them differently? In your rules it should both return (Project OH M-1) + (MTG RF to Order + Total DC Demand). What is (Project OH M-1)?
Alexander

[b_bui]

Hi Alex,
I think my solution using ■■■ toal is cause confusing.
My objective is to calculate project month end stock & how much demand we can support using DC Actual SOH + Total DC Supply - Total DC demand - MTG RF to Order.

[AlexanderFillbrunn]

Hi,
I think I understand a bit better now. You need to access previously calculated numbers, right? Could you use the Recursive Loop Start and Recursive Loop End nodes to do the calculation step-by-step?
Kind regards,
Alexander

[Daniel_Weikert]

Lag Column or Column Expression could be used to get the prior rows value. For cumulative computation moving aggregation (one of my favorites) can be used. Maybe you like to have a look at those nodes
br

[b_bui]

Thank for advise. Lag Column can save me from Column Expression. I tried it. My question is how to restart Lag Column?

From Line 5, PYNF010000-SPRM YTH PWR MLK LTN 100ML/3.4FLOZ-Hong Kong Affiliate
how can I restart Lag-1 for Hong Kong?

[b_bui]

I found solution