Creating history of stock balace

ewer_silvaewer_silva Member Posts: 2 Contributor I
edited December 2018 in Help

Hi guys,

Hope you are doing well.

I have been trying to recreate a history of stock balance, but so far, even consulting some people with good experience in rapidminer, I haven’t been able to do what I need. Maybe you guys would be able to help me.

Below is one example set. Basically, I have initial stock on 02/04. Based on despatches and receipts of the previous days I need to calculate the initial stock for 01/04 and 31/03… all the processes I have tried would work with one product code only, but when the date changes to the next product code it gives incorrect values.

 

Code Initial Stock on 02/04 Trx Data Trx Time Trx Type Adjust
SAN01 100 01/04/2018 21:00 Despatched -10
SAN01 100 01/04/2018 20:00 Received 5
SAN01 100 01/04/2018 19:00 Despatched -7
SAN01 100 31/03/2018 17:00 Despatched -6
SAN01 100 31/03/2018 16:00 Despatched -3
SAN01 100 31/03/2018 15:00 Received 7
SAN02 120 01/04/2018 21:00 Despatched -2
SAN02 120 01/04/2018 20:00 Received 3
SAN02 120 01/04/2018 19:00 Despatched -5
SAN02 120 31/03/2018 17:00 Despatched -6
SAN02 120 31/03/2018 16:00 Despatched -2
SAN02 120 31/03/2018 15:00 Received 6

 

Below is what I expect to get.

SAN01 Date Initial Stock
SAN01 01/04/2018 88
SAN01 01/03/2018 86
SAN02 01/04/2018 116
SAN02 01/03/2018 114

 

thank you all in advance.

 

 

Best Answers

  • Thomas_OttThomas_Ott RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,761 Unicorn
    Solution Accepted

    @ewer_silva You'll need the Aggregate operator. Group by Code and Date, and then use Sum as the aggreation function for Adjust and/or initial stock. Maybe you'll need a Generate Attributes operator too, to do the initial addition/subtraction of the stock and adjustment values. 

  • Thomas_OttThomas_Ott RapidMiner Certified Analyst, RapidMiner Certified Expert, Member Posts: 1,761 Unicorn
    Solution Accepted

    @ewer_silva If you want CumSum, you have to use the Value Series. There's an operator in there to do culmulative summing. 

Answers

  • ewer_silvaewer_silva Member Posts: 2 Contributor I
    Hi Thomas,
    Thanks for that. I had done as you advised, using aggregate and generate attributes, but there was still something missing. My cummulative sum would not restart for new new/different sku. I figured out that I needed a loop that would keep doing the cummulative sun until a new sku came up. I found one example searching the forum. All working fine now ;)

    Thanks for replying.
    Best regards,
    Ewerton
Sign In or Register to comment.