topic Re: Monthly Inventory with different levels, how to calculate? in QlikView

Monthly Inventory with different levels, how to calculate?

Wed, 11 Feb 2015 15:43:29 GMT

Hi guys!

I’m new on Qlikview and I’m really stock with a issue, hope you could help me!

My table looks like this:

DATE	WAREHOUSE	GROUP1	GROUP2	QTY	CUMULATIVE
01/14/2014	W1	G1-1	G2 - 1	20	20
01/15/2014	W1	G1-1	G2 - 1	3	23
03/20/2014	W1	G1-1	G2 - 1	-23	0
01/05/2014	W1	G1-1	G2-2	5	5
03/10/2014	W1	G1-1	G2-2	7	7

What I need to do is to calculate the stock per month for Group2. To do so, I should get the next result:

DATE	WAREHOUSE	GROUP1	GROUP2	TOTAL
01/31/2014	W1	G1-1	G2 - 1	23
02/28/2014	W1	G1-1	G2 - 1	23
03/31/2014	W1	G1-1	G2 - 1	0
01/31/2014	W1	G1-1	G2-2	5
02/28/2014	W1	G1-1	G2-2	5
03/31/2014	W1	G1-1	G2-2	12

My problems are:

I don’t have movements in all months
I have many combinations of warehouses, group1 and group 2 (near of 120,000!)

At the end, I will show only the Groups2 that have Total >0 in each month. Any idea how to get it?

Thanks in advance!

Re: Monthly Inventory with different levels, how to calculate?

rubenmarin — Wed, 11 Feb 2015 16:02:11 GMT

Hi Alma, for date dimension you can use:

=Date(Floor(MonthEnd(DATE)))

and the 'TOTAL' expression can be:

If(Above(TOTAL GROUP2)=GROUP2, Above(TOTAL [TOTAL]) + sum(QTY), sum(QTY))

This will work if table is sorted by group2.

And to get dates for all months you need to generate that missing data in QV or wait for another user to give you a better solution.

Generating Missing Data In QlikView

To get rows with TOTAL>0, if TOTAL is the unique expression, you only need to check "Supress zero values" on presentation tab

Re: Monthly Inventory with different levels, how to calculate?

Wed, 11 Feb 2015 22:16:08 GMT

Gracias Ruben!

Do you know another way to generate just the months I don't have? Because the process will last at least two hours! And the lenght of the project will be much bigger than before

Re: Monthly Inventory with different levels, how to calculate?

rubenmarin — Thu, 12 Feb 2015 08:54:57 GMT

So far I know, you'll need at last one month for each combination of warehouse, group1 and group2, playing with script you can generate only missed data, (120.000*missed months) is a lot of data but I don't know other way.

I keep message in english, easier for other users to give help to generate that data.