topic How to calculate Remaining QTY by dimension? in QlikView

How to calculate Remaining QTY by dimension?

Wed, 27 Nov 2013 07:27:16 GMT

Hi, i have a table in QV:

ProductID	ShopID	DateID	QTY
531	101	01.01.2013	20
531	101	02.01.2013	-1
531	101	05.01.2013	-2
531	102	02.01.2013	10
531	102	04.01.2013	-5
531	101	07.01.2013	-2
531	102	08.01.2013	3
531	101	08.01.2013	-2
531	102	10.01.2013	-1
531	102	11.01.2013	-2

Need to calculate Remaining QTY by ShopID, DateID.

The resulting table should look like this:

ProductID	ShopID	DateID	QTY	RemainingQTY
531	101	01.01.2013	20	20
531	101	02.01.2013	-1	19
531	101	05.01.2013	-2	17
531	102	02.01.2013	10	10
531	102	04.01.2013	-5	5
531	101	07.01.2013	-2	15
531	102	08.01.2013	3	8
531	101	08.01.2013	-2	13
531	102	10.01.2013	-1	7
531	102	11.01.2013	-2	5

Re: How to calculate Remaining QTY by dimension?

Wed, 27 Nov 2013 08:01:49 GMT

Hi,

The way you show your result is not that easy to achieve.

Maybe you would consider a more easy method.

For example see my attached file

Regards,

Gerrit

Re: Re: How to calculate Remaining QTY by dimension?

Wed, 27 Nov 2013 08:24:06 GMT

Thank you!

But unfortunately i need to get a division remaining QTY by ShopID in the form of a graph.

I can calculate the RemainingQTY of the whole on all ShopID.

Example in the attachment.

Calculation formula in this case would be the following :

RemQTY = alt(Above([RemQTY]),SUM({<DateID={"<$(=MIN(DateID))"}>}TOTAL QTY)) + SUM(QTY)

Re: How to calculate Remaining QTY by dimension?

udit_kumar_sana — Wed, 27 Nov 2013 08:27:42 GMT

Hi,

Pls Find the attached qvw for the solution.

Thanks & Regards,

Udit

Re: How to calculate Remaining QTY by dimension?

udit_kumar_sana — Wed, 27 Nov 2013 08:29:38 GMT

hi ,

Pls find the attached qvw for the solution

Thanks & Regards,

Udit

Re: Re: Re: How to calculate Remaining QTY by dimension?

udit_kumar_sana — Wed, 27 Nov 2013 08:36:48 GMT

For the line chart way pls find the attachment

Re: How to calculate Remaining QTY by dimension?

tresB — Wed, 27 Nov 2013 08:46:48 GMT

In the script, something like:

Load *,
If(ShopID=Peek('ShopID'), RangeSum(Peek('QTY'),QTY), QTY) As RemQTY;

load * Inline [
ProductID, ShopID, DateID, QTY
531, 101, 01.01.2013, 20
531, 101, 02.01.2013, -1
531, 101, 05.01.2013, -2
531, 102, 02.01.2013, 10
531, 102, 04.01.2013, -5
531, 101, 07.01.2013, -2
531, 102, 08.01.2013, 3
531, 101, 08.01.2013, -2
531, 102, 10.01.2013, -1
531, 102, 11.01.2013, -2
];

Note: You might have to use ORDER BY clause based on your original data.

PFA