topic Stock problem in QlikView

Stock problem

Tue, 29 Nov 2011 11:05:23 GMT

Hi everyone,

I have some trouble so find out a solution on my problem, so i'll explain it to you and hope someone can help me. 🙂

I am building a QV application to manage my client stock. I have a DB which save all the mouvement for each product.

It's something like that.

Mouvement Date	Client	Product reference	Num Mouvement	Final Stock
13/05/2011	C1	PR001	9	1
13/05/2011	C1	PR001	8	3
14/06/2010	C1	PR001	6	1
20/12/2008	C1	PR001	2	2
20/10/2008	C1	PR001	1	3
29/11/2011	C1	PR002	10	5
13/01/2011	C1	PR002	7	1
13/04/2010	C1	PR002	5	3
26/08/2009	C1	PR002	4	5
13/03/2009	C1	PR002	3	4
13/04/2011	C1	PR003	11	1
29/05/2011	C2	PR001	4	2
19/02/2011	C2	PR001	3	3
01/12/2010	C2	PR001	2	0
06/09/2009	C2	PR001	1	2

I already find a way to build a chart with the final stock for each client and product for a given date. I am using the FirstSortedValue() on the Num Mvt and my chart use two dimension (Client and Product). This chart well represents my final stock for a given.

What I want do know is a bart chart representing the evolution of my stock during the time.

I know what I must do but I can find a solution of how to do it. For each date of my dimension I need to get the last mouvement before this date for each client-product and then sum the final stock. For example, I must have on my bar of february 2010 the value 9 and for the year 2008 the value 2.

I hope it's enough clear to get the point and show me a way to find out a solution.

Regards,

Guillaume

Stock problem

christophebrault — Tue, 29 Nov 2011 13:45:50 GMT

Hi,

you can try something like Above(sum(Num Mouvement),1) in your chart to have the last value.

But i don't really understand your data...

Stock problem

Tue, 29 Nov 2011 13:53:23 GMT

I'm gonna try that.

About data :

Each line is a mouvement in my stock, a sale or a purchase.

For example, I read the third first line :

- at the end of the mouvement number 6, client 1 has 1 PR001 product in his stock

- then he buy two of this product the 13/05/2011. He has then 3 piece of this product in his stock

- the same day he sales two PR001 product. His final PR001 stock his then 1

Hope it helps to understand better

Stock problem

christophebrault — Tue, 29 Nov 2011 14:12:16 GMT

Ok i understand.

So you can do sum(Final Stock)-Above(sum(Final Stock),1)

It gives you the number of products of the last mouvement.