Hello all. I have inventory data that includes some columns like this:
| id | type | quantity | received_on | sent_on |
|---|
| 1 | A | 2 | 21-1-2017 | 15-2-2017 |
| 2 | B | 3 | 21-1-2017 | 20-3-2017 |
| 3 | B | 2 | 21-1-2017 | |
| 4 | C | 1 | 21-1-2017 | 13-11-2017 |
| 5 | D | 3 | 21-1-2017 | |
I want to produce a pivot table showing the quantity of each type of item on hand at the end of each month.
row: type
column: monthend of each month
measure: quantity in stock (i.e. sum quantity if the item was received before the column's monthend and was either never sent or was sent after that column's monthend)
I hope that makes some sense... any help appreciated.
