Calculate the rollover qty backwards
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Data Investigation
- Datasets
- Developer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Ronal_bal How is it that the expected output for the first record is 4409? Where did that come from?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you help me with this request? @Prometheus
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Ronal_bal Is the column Expected Output the actual expected output for each row or is it a part of the dataset?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Prometheus it is the expected output. Not a part of the dataset.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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.