Calculate the rollover qty backwards

[Ronal_bal]

Hi,

I'm trying to calculate the rollover qty based on the following criteria's,

The calculation needs to start from the last record and its cum_received value. 

for the last records, use the cum_received as the expected output

Whenever the current status is delayed then use the next cum. 

whenever the current status is ahead or okay then use next_cum-current_qty

Attached the example in the excel.

[Prometheus]

@Ronal_bal How is it that the expected output for the first record is 4409? Where did that come from?

[Ronal_bal]

@Prometheus becuase the current status is ahead, so row+1:Expected Output -received qty. which is 4410-1 = 4409. 

Sorry by next cum i mean next expected output.

[Ronal_bal]

Can you help me with this request? @Prometheus

[Prometheus]

@Ronal_bal Is the column Expected Output the actual expected output for each row or is it a part of the dataset?

[Ronal_bal]

@Prometheus it is the expected output. Not a part of the dataset.

[caltang]

Like so:

[Ronal_bal]

@caltang thanks for the help. It works as expected, but what if we had only one record as the input? 

The current logic just takes the maximum tile value as the first value for the expected output. That works fine in cases when you have multiple shipments and the most recent shipment with status=delayed on the same day.

What if we had only one shipment like below and shipment status= 'ahead' or  'ok'? 

Thanks again for your help and appreciate your support!

[caltang · Accepted solution]

I've changed the logic ever so slightly here by updating the first Join to include your Unique Key:

[Ronal_bal]

@caltang I believe the first logic you provided is working as expected. 

Can you please look for the second request I posted. 

Attached the excel file with expected result for case 2.