Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
];
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
];
thank you sunindia.
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
by mistake
Hahahaha okay
Made me wonder what was so helpful about your own thank you