Pivot table showing quantity of each item type on hand at end of each month
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.
