Calculation between rows

Simon_BI — Fri, 27 Dec 2019 07:50:48 GMT

I have raw table1 as below. ITEM_No is product identity; Qty is the need for each STO; Stocks is the same for each product.

Below Table2 is the result i want to get. For each distinct article, when the Stocks_Remaining below zero, i wish to have a comment as "N" and before the Stocks_Remaining reach zero, i wish to have a comment as "Y" for these stores.

Attached are an QVW file incudling the raw data and an excel file that shows how we do it in excel.

I have no idea how to get this result table through Qlikview. Could someone help here in QLIKVIEW?

Table1
ITEM_NO	STO Number	QTY	Stocks
00169560	001	8	200
00169560	002	8	200
00169560	003	8	200
00169560	004	8	200
00169560	005	8	200
00169560	006	8	200
00169560	007	16	200
00169560	008	8	200
00169560	009	8	200
00169560	010	8	200
00169560	011	8	200
00169560	012	8	200
00169560	013	8	200
00169560	014	8	200
00169560	015	16	200
00169560	016	16	200
00169560	017	8	200
00169560	018	8	200
00169560	019	8	200
00169560	020	8	200
00169560	021	16	200
00169560	022	16	200
00169560	023	8	200
00169560	024	8	200
00169560	025	8	200
00169560	026	8	200
00169560	027	16	200
00169560	028	24	200
30234229	001	10	20
30234229	002	10	20
30234229	003	10	20
30234229	004	10	20
30234229	005	10	20
30234229	006	10	20
30234229	007	10	20
30234229	008	10	20
30234229	009	10	20
30234229	010	10	20

Table2
ITEM_NO	STO Number	QTY	Stocks	StocksRemaining_Calculation	Decision
00169560	001	8	200	192	Y
00169560	002	8	200	184	Y
00169560	003	8	200	176	Y
00169560	004	8	200	168	Y
00169560	005	8	200	160	Y
00169560	006	8	200	152	Y
00169560	007	16	200	136	Y
00169560	008	8	200	128	Y
00169560	009	8	200	120	Y
00169560	010	8	200	112	Y
00169560	011	8	200	104	Y
00169560	012	8	200	96	Y
00169560	013	8	200	88	Y
00169560	014	8	200	80	Y
00169560	015	16	200	64	Y
00169560	016	16	200	48	Y
00169560	017	8	200	40	Y
00169560	018	8	200	32	Y
00169560	019	8	200	24	Y
00169560	020	8	200	16	Y
00169560	021	16	200	0	Y
00169560	022	16	200	-16	N
00169560	023	8	200	-24	N
00169560	024	8	200	-32	N
00169560	025	8	200	-40	N
00169560	026	8	200	-48	N
00169560	027	16	200	-64	N
00169560	028	24	200	-88	N
30234229	001	10	20	10	Y
30234229	002	10	20	0	Y
30234229	003	10	20	-10	N
30234229	004	10	20	-20	N
30234229	005	10	20	-30	N
30234229	006	10	20	-40	N
30234229	007	10	20	-50	N
30234229	008	10	20	-60	N
30234229	009	10	20	-70	N
30234229	010	10	20	-80	N

Re: Calculation between rows

Taoufiq_Zarra — Fri, 27 Dec 2019 13:40:54 GMT

Hi,

Please find in attached the Qlikview file

output:

Re: Calculation between rows

Simon_BI — Mon, 30 Dec 2019 01:39:41 GMT

Hi Taoufig,

That works and help me to expand my knowledge.

Really thanks for your professional answer.

topic Re: Calculation between rows in QlikView

Calculation between rows

Re: Calculation between rows

Re: Calculation between rows