Hi, I'm new to the forum and I'm Italian so sorry for the bad English.
However, I want to calculate the stock rotation index for the items of my warehouse.
The available tables are:
- FACT_STOCK: the current stock for the items in each warehouse is periodically measured and registered here
| STK_ID | STK_ITM_ID | STK_WHS_ID | STK_QTY | STK_DATE |
| 1 | I0001 | WH1 | 40 | 1 Jan 18 |
| 2 | I0001 | WH1 | 40 | 2 March 18 |
| 3 | I0001 | WH1 | 37 | 5 June 18 |
| 4 | I0002 | WH2 | 30 | 2 March 18 |
The table does not contain the stock quantity of each item. Some of them are missing.
- FACT_MOV: contains all the movements (sales, transfers, returns) and the correspondent quantity and date
| STK_ID | MOV_CAU | MOV_ITM_ID | MOV_WHS_ID | MOV_QTY | MOV_DATE |
| A1 | Sale | I0001 | WH1 | 12 | 12 Dec 17 |
| A2 | Sale | I0001 | WH1 | 24 | 20 Dec 17 |
| A3 | Sale | I0001 | WH1 | 3 | 10 April 18 |
| A4 | Return | I0001 | WH1 | 10 | 8 Jul 18 |
The update of the stock is not immediate: for example, on 2nd March 2018 the stock level of I0001 was of 40 pieces; in the next month (10th April 2018) 3 pieces were sold and in the next month (5th June 2018) the stock level was updated.
- MASTER_ITEM: each item belongs to a group of product and each group belongs to a specific category
ITM_ID | ITM_GROUP | ITM_CATEGORY |
| I0001 | Wine | Blue |
| I0002 | Milk | Blue |
| I0003 | Beer | Red |
How can I calculate the stock rotation index as total quantity sold divided by average stock?
