topic Presenting stock values / quantities from a transaction based table in QlikView

Presenting stock values / quantities from a transaction based table

Tue, 16 Nov 2010 09:23:59 GMT

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?

Please release me of this bugger.

Thanks,

Johan Vermeulen

K3 Business Solution

Netherlands

Presenting stock values / quantities from a transaction based table

deepakk — Tue, 16 Nov 2010 11:13:14 GMT

Hi Johan,

I hope the sample application helps you out.

Let me know if you have any queries on the same

Presenting stock values / quantities from a transaction based table

Wed, 17 Nov 2010 12:43:49 GMT

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

Thanks again for your time.

Johan.

Presenting stock values / quantities from a transaction based table

Wed, 17 Nov 2010 15:07:14 GMT

I found how you set the maxdate variable. Simple and functional, that's what I'm looking for.

A solution for the 'do not show the months prior the selected period' would finish it off!

Presenting stock values / quantities from a transaction based table

deepakk — Wed, 24 Nov 2010 06:18:33 GMT

Hi,

In order to achieve the two month selection , you need to calculate the stock at script level using Peek function.

Try it out .. if you are not able to acheive it

I will help youo out in the same.