Confining Rolling Sum to a Specific Number

[MD2050]

Hello -

we have a work schedule which is split by "BatchFlag" , we need to confine the SumQty to 100 so if a batch is less then 100 then it should add units form subsequent "same" batch row and round it to 100 and move the remainder quantity to the next "same" batch making sure that the cumulative sum of that particular batch remains same. 

I am trying to add a multi-row logic to add a new variable "batch" # based on the batch flag and then check the SumQty for 100 ; if <100 then add from next batch row. Unfortunately i am not able to get around the logic of not adding if sumqty>100 and leave the SumQty as-is. 

left snippet shows what i am working with , right snippet shows the data and "Required" column shows the desired output. 

Thank you for helping. 

[Thableaus]

Hi @MD2050 

replace "elseif sumqty>100 then"

by

"else [SumQty]"

Also, uncheck the GroupBy "Batch". GroupBy "BatchFlag" should be the right one.

Cheers,

[MD2050]

Hello  @Thableaus -

Thank you for quick reply, unfortunately the logic didn't worked out. Please see the output below , we have to also make sure that the cumulative sum of the batch remains intact  only allocation needs to be changed when the batch total exceeds 100. Thanks

[Thableaus]

@MD2050 

I think the logic you were trying to apply won't work as you're thinking it would.

Just explain me something: why at the last line of the BatchFlag 015700C1 the Required SumQty for 80 is 60? I didn't get that.

Cheers,

[MD2050]

The cumulative sum needs to remain same. For example -  for batch flag "015700C1" the total quantity is 1160 which is broken down in 10*100 batches and 2*80 batches - we need batches of 100 so in the "Required" we should have 11*100 and 1*60 . Hope i was able to explain. 

Thanks for your help. 

[Thableaus]

@MD2050 

Here's a proper solution for your case:

- First you calculate total quantity per date and per batch - Then you find 1160 and 272 for each of them.

- Join this total sum to each line of your dataset - joining by SchDate and BatchFlag

- Create Required field = 100. We'll start with 100 to every row.

- Update the Total_Sum_PerBatch field with the following logic:

Row before of Total_Sum_PerBatch - Required (100) -> this is to find out where we will distribute the remainders.

- Update Required field with following logic:

IF the Row before of the new Total_Sum_PerBatch is negative (<0), then we know there's nothing left of the remainder. So Required will be updated to 0.

ELSEIF the Total_Sum_PerBatch is <0, then that means we have to get the row before that value (the last remainder). 

ELSE we keep the value (100) of Required.

See the workflow attached. I hope this works well for you. Be aware of the details.

Cheers,

[MD2050 · Accepted solution]

Hi @Thableaus -

Apologies for late reply. I retrofitted your logic to my program and it worked good as long as i didn't encounter single row entry of "batchflag" in that case your logic didn't work correctly,my fault for not providing you the complete data as i didn't knew it will cause an issue- sorry. 

I however took part of your logic where you summarized the quantities and added another variable "required" (which served as a constraint variable) .I used a MOD function to calculate the remainder then calculated "# of rows" for those "batchflags" whose quantities divided evenly followed by "generate rows" tool and with this i was able to achieve the required.  

Please find in attached my solution. Thank you for helping me.

md.

[Thableaus]

@MD2050 

No problem.

Please share your workflow as package, so I can see data on it.

Options > Export Workflow.

Cheers,

[MD2050]

Hi-

Please see attached the data,i already uploaded my WF in the previous email. 

Thanks

[Thableaus]

@MD2050 

This csv data doesn't match the one in the workflow you shared.

I know you exported your workflow, but it becomes useless if you don't export as .yxzp package, because the Input Tool is referencing the file to a local folder of yours.

Please share the packaged workflow with data.

Cheers,