Presenting stock values / quantities from a transaction based table
I'm having sleepless nights how to solve this.
Goal is to show the current inventory of items, or the inventory at the begin and end of a selected period. Filters on location might apply. (QV 9 SR6)
Source table shows:
item
location date
transaction
quantity
A
X
01.01.10
purchase
3
A
X
01.02.10
purchase
2
A
X
01.03.10
sale
-1
A
Y
10.02.10
purchase
4
B
X
20.02.10
purchase
5
B
X
20.03.10
sale
-3
The stock quantities are
item
location
date
stock
A
X
01.01.10
3
A
X
01.02.10
5
A
X
01.03.10
4
A
Y
10.02.10
4
B
X
20.02.10
5
B
X
20.03.10
2
The stock quantities on 25.02.10 are
item
location
stock
A
X
5
A
Y
4
B
X
5
Stock per location on 15.02.10
location
stock
X
5
Y
4
Now, the following dashboard objects must be created:
. Line graph, indicating the quantity movement in time. Possibly with a location or item selection
. Bar chart, showing stock per location, possibly with an item or period selection. The value at the end of the period must be shown
. Pivot table showing the stock at the start and at the end of a selected period.
Big question: how to get from the source table to the objects on the dashboard?
Thanks Deepak. Really useful to see you solve this by using 'full accumulation'. I tried that, but apparantly not in the correct way.
just a few questions on your solution:
. where do you set the vMaxDate . You set the beginning of the selection by adding {<Year=,Month=,Day=,[Tran Date]= {"<=$(vMaxDate)"}>} to the sum funtion of Qty. Is there also a way to set it in a way that it does NOT show the month before the selected period.
Like: selection = March + April table and graph show: only march and april (NOT Jan and Feb) Values: 01 febr: 6464.40 01 march: 6064.40 30 april: 7057.40
