Hello all.
I'm looking to incorporate a cumulative average in the script.
My table looks in a very basic way like:
| Customer | Article | Date | Sales_date | Shipmentreceived_date | QtySold | QtyReceived |
| A | A | 01-01-20 | | 01-01-20 | | 5 |
| A | A | 05-01-20 | 05-01-20 | | 2 | |
| A | A | 10-01-20 | 10-01-20 | | 1 | |
| A | A | 16-01-20 | | 16-01-20 | | 3 |
| A | A | 18-01-20 | 18-01-20 | | 4 | |
| B | A | 01-01-20 | | 01-01-20 | | 5 |
| B | A | 04-01-20 | 04-01-20 | | 3 | |
| A | B | 01-01-20 | | 01-01-20 | | 10 |
| B | B | 02-01-20 | | 02-01-20 | | 15 |
I'm trying to calculate the average stock level per date. The result should be something like this:
| Customer | Article | Date | Sales_date | Shipmentreceived_date | QtySold | QtyReceived | QtySold_Cumul | QtyReceived_Cumul | Qty_stock_on_date |
| A | A | 01-01-20 | | 01-01-20 | | 5 | | 5 | 5 |
| A | A | 05-01-20 | 05-01-20 | | 2 | | 2 | 5 | 3 |
| A | A | 10-01-20 | 10-01-20 | | 1 | | 3 | 5 | 2 |
| A | A | 16-01-20 | | 16-01-20 | | 3 | 3 | 8 | 5 |
| A | A | 18-01-20 | 18-01-20 | | 4 | | 7 | 8 | 1 |
| B | A | 01-01-20 | | 01-01-20 | | 3 | | 3 | 3 |
| B | A | 04-01-20 | 04-01-20 | | 2 | | 2 | 3 | 1 |
| A | B | 01-01-20 | | 01-01-20 | | 10 | | 10 | 10 |
| B | B | 02-01-20 | | 02-01-20 | | 15 | | 15 | 15 |
So I would like to add rows for cumulative sales per customer per article and cumulative shipments received per customer per article. These should be subtracted from each other defined by a time period (per date, or maybe week(date)). So I can get a stock average per week. So if I check the stock for 11-01-20 for customer A and article A it will show a stock level of 3.
Is there a simple way of doing this?
.png "Former Employee"){kind=icon}
