5 Replies Latest reply: Mar 30, 2016 10:50 AM by Sunny Talwar

# Calculate from Upper Row Value

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

May be something along these lines

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();

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

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

thank you stalwar1.

• ###### Re: Calculate from Upper Row Value

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

by mistake

• ###### Re: Calculate from Upper Row Value

Hahahaha okay