topic Re: Calculate from Upper Row Value in QlikView

Calculate from Upper Row Value

imrencimen — Wed, 23 Mar 2016 10:04:15 GMT

I have ıtems and demand.

I want to find which demands are provided by me. So that ı find total items stock and distribute that by demans and delivery time. Distributing continues untill stock are run out of ıtems.

I didn't calculate NewStock and So that PreviousNewStock in Qlik. just find red written row.(first rows of Items).

qwd file and excel are attached.

If(Bstock='BB',Previous(NewStock),Bstock) as BStock. but ı cant do it because of duplicate name Bstock.

How can I Calculate "BStock,Previous(NewStock),NewStock" Rows?

Customer	Item	Previous Item	DATE	WEEK	BStock	Previous(NewStock)	Demand	can be dispatch	New Stock
BBB	zzzzzzzzzzz		25.02.2016	2016-08	10		2	2	8
BBB	xxxxxxxxxx	zzzzzzzzzzz	25.02.2016	2016-08	202		2	2	200
BBB	xxxxxxxxxx	xxxxxxxxxx	14.03.2016	2016-11	BB	BB=previous(newstock)=200	2	2	198
İSTB	xxxxxxxxxx	xxxxxxxxxx	22.03.2016	2016-12	BB	BB=previous(newstock)=198	55	55	143
BBB	xxxxxxxxxx	xxxxxxxxxx	28.03.2016	2016-13	BB	BB=previous(newstock)=143	4	4	139
BBB	xxxxxxxxxx	xxxxxxxxxx	28.03.2016	2016-13	BB	BB=previous(newstock)=139	10	10	129
BBB	xxxxxxxxxx	xxxxxxxxxx	28.03.2016	2016-13	BB	BB=previous(newstock)=129	16	16	113
BBB	xxxxxxxxxx	xxxxxxxxxx	28.03.2016	2016-13	BB	BB=previous(newstock)=113	20	20	93
BBB	xxxxxxxxxx	xxxxxxxxxx	04.04.2016	2016-14	BB	BB=previous(newstock)=93	2	2	91
GAR	xxxxxxxxxx	xxxxxxxxxx	11.04.2016	2016-15	BB	BB=previous(newstock)=91	1	1	90
BBB	xxxxxxxxxx	xxxxxxxxxx	11.04.2016	2016-15	BB	BB=previous(newstock)=90	10	10	80
BBB	xxxxxxxxxx	xxxxxxxxxx	18.04.2016	2016-16	BB	BB=previous(newstock)=80	2	2	78
BBB	xxxxxxxxxx	xxxxxxxxxx	18.04.2016	2016-16	BB	BB=previous(newstock)=78	6	6	72
BBB	xxxxxxxxxx	xxxxxxxxxx	18.04.2016	2016-16	BB	BB=previous(newstock)=72	10	10	62
BOY	xxxxxxxxxx	xxxxxxxxxx	18.04.2016	2016-16	BB	BB=previous(newstock)=62	30	30	32
BBB	xxxxxxxxxx	xxxxxxxxxx	25.04.2016	2016-17	BB	BB=previous(newstock)=32	2	2	30
BBB	xxxxxxxxxx	xxxxxxxxxx	25.04.2016	2016-17	BB	BB=previous(newstock)=30	8	8	24
GÜR	xxxxxxxxxx	xxxxxxxxxx	25.04.2016	2016-17	BB	BB=previous(newstock)=24	19	19	5
TEL	xxxxxxxxxx	xxxxxxxxxx	25.04.2016	2016-17	BB	BB=previous(newstock)=5	30	5	0
BOY	xxxxxxxxxx	xxxxxxxxxx	25.04.2016	2016-17	BB	BB=previous(newstock)=0	100	0	0
BBB	yyyyyyyyyy	xxxxxxxxxx	21.03.2016	2016-12	114	114	4	4	110
BBB	yyyyyyyyyy	yyyyyyyyyy	21.03.2016	2016-12	BB	110	2	2	108
DOU	yyyyyyyyyy	yyyyyyyyyy	22.03.2016	2016-12	BB	108	50	50	58
BBB	yyyyyyyyyy	yyyyyyyyyy	28.03.2016	2016-13	BB	58	2	2	56
BBB	yyyyyyyyyy	yyyyyyyyyy	28.03.2016	2016-13	BB	56	6	6	50
BBB	yyyyyyyyyy	yyyyyyyyyy	28.03.2016	2016-13	BB	50	14	14	36
BBB	yyyyyyyyyy	yyyyyyyyyy	28.03.2016	2016-13	BB	36	8	8	28
BBB	yyyyyyyyyy	yyyyyyyyyy	28.03.2016	2016-13	BB	28	30	28	0
BBB	yyyyyyyyyy	yyyyyyyyyy	28.03.2016	2016-13	BB	0	40	0	0
TEL	yyyyyyyyyy	yyyyyyyyyy	28.03.2016	2016-13	BB	0	34	0	0
GAR	yyyyyyyyyy	yyyyyyyyyy	04.04.2016	2016-14	BB	0	8	0	0

Re: Calculate from Upper Row Value

sunny_talwar — Wed, 23 Mar 2016 12:03:18 GMT

May be something along these lines

LOAD *,

If(If(ITEM <> Peek('ITEM'), BStock - DEMAND, Peek('PreviousNewStock') - (DEMAND+Peek('DEMAND'))) < 0, 0,

If(ITEM <> Peek('ITEM'), BStock - DEMAND, Peek('PreviousNewStock') - (DEMAND+Peek('DEMAND')))) as NewStock,

If(ITEM <> Peek('ITEM'), BStock, Peek('NewStock')) as PreviousNewStock,

RowNo();

LOAD *,

If(ITEM <> Peek('ITEM'), ApplyMap('app',ITEM,'Not'),'BB') as BStock;

LOAD * INLINE [

Customer,ITEM , PreviousItem, DATE, WEEK, DEMAND

BBB, zzzzzzzzzzz, , 25.02.2016, 2016-08, 2

BBB, xxxxxxxxxx, zzzzzzzzzzz, 25.02.2016, 2016-08, 2

BBB, xxxxxxxxxx, xxxxxxxxxx, 14.03.2016, 2016-11, 2

İSTB, xxxxxxxxxx, xxxxxxxxxx, 22.03.2016, 2016-12, 55

BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 4

BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 10

BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 16

BBB, xxxxxxxxxx, xxxxxxxxxx, 28.03.2016, 2016-13, 20

BBB, xxxxxxxxxx, xxxxxxxxxx, 04.04.2016, 2016-14, 2

GAR, xxxxxxxxxx, xxxxxxxxxx, 11.04.2016, 2016-15, 1

BBB, xxxxxxxxxx, xxxxxxxxxx, 11.04.2016, 2016-15, 10

BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 2

BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 6

BBB, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 10

BOY, xxxxxxxxxx, xxxxxxxxxx, 18.04.2016, 2016-16, 30

BBB, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 2

BBB, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 8

GÜR, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 19

TEL, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 30

BOY, xxxxxxxxxx, xxxxxxxxxx, 25.04.2016, 2016-17, 100

BBB, yyyyyyyyyy, xxxxxxxxxx, 21.03.2016, 2016-12, 4

BBB, yyyyyyyyyy, yyyyyyyyyy, 21.03.2016, 2016-12, 2

DOU, yyyyyyyyyy, yyyyyyyyyy, 22.03.2016, 2016-12, 50

BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 2

BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 6

BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 14

BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 8

BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 30

BBB, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 40

TEL, yyyyyyyyyy, yyyyyyyyyy, 28.03.2016, 2016-13, 34

GAR, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 8

MOB, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 50

TEL, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 25

KAR, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 48

BOY, yyyyyyyyyy, yyyyyyyyyy, 04.04.2016, 2016-14, 40

BBB, yyyyyyyyyy, yyyyyyyyyy, 11.04.2016, 2016-15, 2

];

Re: Calculate from Upper Row Value

imrencimen — Fri, 25 Mar 2016 08:10:53 GMT

thank you sunindia‌.

Re: Calculate from Upper Row Value

sunny_talwar — Wed, 30 Mar 2016 14:20:25 GMT

What is the point of marking your own answer as helpful and that too a thank you response?

I would suggest you to wisely use correct and helpful answers for only those posts that are actually helpful. Read here: Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Calculate from Upper Row Value

imrencimen — Wed, 30 Mar 2016 14:23:08 GMT

by mistake

Re: Calculate from Upper Row Value

sunny_talwar — Wed, 30 Mar 2016 14:50:46 GMT

Hahahaha okay

Made me wonder what was so helpful about your own thank you