Calculate from Upper Row Value

[imrencimen]

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

[sunny_talwar · Accepted solution]

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

];

[imrencimen]

thank you sunindia‌.

[sunny_talwar]

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

[imrencimen]

by mistake

[sunny_talwar]

Hahahaha okay

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