topic Re: Calculation using previous row value in QlikView

Calculation using previous row value

Wed, 17 Dec 2014 15:35:34 GMT

Dear Experts

I need to calculate the column “Risky stock” as shown in the attached excel file.

The other columns I have them loaded from SQL.

Any help?

Re: Calculation using previous row value

Wed, 17 Dec 2014 15:37:11 GMT

have you tried making use of the above() function?

Re: Calculation using previous row value

Wed, 17 Dec 2014 15:38:37 GMT

The above function does mot reset when the item changes.

Re: Calculation using previous row value

Wed, 17 Dec 2014 15:45:05 GMT

If the above function can reset when the item changes please tell me how it will helps

Re: Calculation using previous row value

Wed, 17 Dec 2014 15:47:09 GMT

Could you include a sample qvw to show what your problem is?

Re: Calculation using previous row value

preminqlik — Wed, 17 Dec 2014 16:33:22 GMT

ORGINAL_DATA:

LOAD *,RowNo() as Rowno INLINE [

Item,Average Sales, Month, Stock

Item 1, 200, 2, 150

Item 1, 200, 3, 250

Item 1, 200, 4, 1000

Item 1, 200, 6, 100

Item 1, 200, 7, 500

Item 1, 200, 10, 750

Item 1, 200, 12, 150

Item 2, 100, 2, 10

Item 2, 100, 10, 1200

Item 2, 100, 2, 100

];

STAGE1:

Load *,

if(rowno()=1,if([P_Average Sales]=[Average Sales],1,Stock-(Month*[Average Sales])),

IF([Average Sales]=[P_Average Sales],IF(Peek('RISK_STOCK')<0,(Stock+Peek('RISK_STOCK'))-((Month-P_Month)*[Average Sales]),Stock-((Month-P_Month)*[Average Sales])),Stock-(Month*[Average Sales]))) as RISK_STOCK;

Load *,

Peek([Average Sales]) as [P_Average Sales],

peek(Month) as P_Month,

Peek(Stock) as P_Stock

Resident ORGINAL_DATA;

DROP table ORGINAL_DATA; !

Re: Calculation using previous row value

Thu, 18 Dec 2014 07:57:37 GMT

I used the syntax you proposed Prem Kumar Thangallapally .

I got strange numbers.

Prem Kumar Thangallapally and Robert Daitzman please find attached the qvw file

Please if anyone can help.

Thank you.

Re: Calculation using previous row value

preminqlik — Thu, 18 Dec 2014 08:47:34 GMT

try with this ..you have took Expdate in expression instead of Exp_month

Stock_New:

LOAD *,RowNo() as Rowno

Resident Stock ;

Drop Tables Stock;

STAGE1:

Load *,

rangesum(if(rowno()=1,if([P_AvrgSales]=[AvrgSales],1,Stock_Qty-(Exp_Month*[AvrgSales])),

IF([AvrgSales]=[P_AvrgSales],IF(Peek('RISK_Stock_Qty')<0,(Stock_Qty+Peek('RISK_Stock_Qty'))-((Exp_Month-P_Monthe)*[AvrgSales]),Stock_Qty-((Exp_Month-P_Exp_Month)*[AvrgSales])),Stock_Qty-(Exp_Month*[AvrgSales]))),0) as RISK_Stock_Qty;

Load *,

Peek([AvrgSales]) as [P_AvrgSales],

peek(Exp_Month) as P_Exp_Month,

rangesum(Peek(Stock_Qty),0) as P_Stock_Qty

Resident Stock_New;

DROP table Stock_New;

Re: Calculation using previous row value

Thu, 18 Dec 2014 10:19:14 GMT

Dear PREMinQLIK

Thank you for your help but I am still getting wrong numbers.

I used the below script:

Stock_New:

LOAD *,RowNo() as Rowno

Resident Stock ;

Drop Tables Stock;

STAGE1:

Load *,

rangesum(if(rowno()=1,if([P_AvrgSales]=[AvrgSales],1,Stock_Qty-(Exp_Month*[AvrgSales])),

IF([AvrgSales]=[P_AvrgSales],IF(Peek('RISK_Stock_Qty')<0,(Stock_Qty+Peek('RISK_Stock_Qty'))

-((Exp_Month-[P_Exp_Month])*[AvrgSales]),Stock_Qty-((Exp_Month-[P_Exp_Month])*[AvrgSales])),Stock_Qty-(Exp_Month*[AvrgSales]))),0) as RISK_Stock_Qty;

Load *,

Peek([AvrgSales]) as [P_AvrgSales],

peek([Exp_Month]) as [P_Exp_Month],

Peek(Stock_Qty) as P_Stock_Qty

Resident Stock_New;

DROP table Stock_New;

and got the below as result (it is only a small part of the data)

SKU	ExDate	Exp_Month	Risky St	Total Stk
ST000046	01-07-15	7	167	127
ST000046	18-07-15	7	4	4
ST000046	01-08-15	8	154	180
ST000047	01-06-15	6	106	66
ST000047	06-06-15	6	34	34
ST000047	01-08-15	8	100	180
ST000048	01-08-15	8	-327	153
ST000054	01-07-15	7	3	3
ST000057	12-06-15	6	-728	2,872
ST000058	01-06-15	6	-2550	150
ST000058	11-06-15	6	-726	1,824
ST000059	01-06-15	6	-509	2,791
ST000059	02-06-15	6	-485	24
STFRSWTDB0250G012ASS	04-07-15	7	-260	1
STLBTSTDB0017G024CHC	08-01-15	1	-249	1
STNGTSTBG0100G012GLF	28-12-14	12	-952	0
STSTBSDAF0190G012GRB	02-03-15	3	-1769	3
STSTBSDSHGFPRO002ASS	21-08-15	8	7	7
STSTBSDSHGFPRO002ASS	01-09-15	9	0	1
STSTCDTDB0080G006COC	01-03-15	3	-227	1
STSTCOKAF0085G012ORG	01-03-15	3	74	72
STSTJAMJR0325G012APR	01-09-15	9	-571	59
STSTOBRAF0250G012PLN	14-07-15	7	-130	10
STSTRCKBG0130G012CHC	20-04-15	4	-159	1
STSTRCKBG0130G012CHC	17-06-15	6	-239	0
STSTRCKBG0130G012CHC	21-07-15	7	-185	93
STSTRCKPK0120G012CHC	22-07-15	7	0	8
STSTSYDDB0001L012SOY	20-06-15	6	-530	130
STSTTSTBG0090G012SOY	01-07-15	7	-1065	9
STSTTSTBG0090G012SOY	10-07-15	7	-820	246
STSTTSTBG0100G012ASS	28-02-15	2	-351	0
STSTTSTBG0130G012CRL	01-07-15	7	-1047	246
STSTTSTBG0130G012CRL	07-07-15	7	-900	147
STSTTSTBG0130G012NST	01-07-15	7	-1026	58
STSTTSTBG0130G012RCE	01-07-15	7	-1584	297
STSTTSTCR0100G012MDT	15-07-15	7	-871	66
STSTTSTDC0250G012BRT	01-01-15	1	-44	1
STSTWFRAF0160G012VNL	01-06-15	6	-215	25
STWFWFBAF0034G024CCN	02-06-15	6	936	936
STWFWFBAF0034G024CCN	01-10-16	10	106	106
STWFWFBAF0034G024COC	14-04-15	4	-1361	839

Re: Calculation using previous row value

Thu, 18 Dec 2014 10:50:10 GMT

I think the problem is that the value is not resetting for new item.

Re: Calculation using previous row value

preminqlik — Thu, 18 Dec 2014 16:12:19 GMT

should reset for only item level ?

both company and item level ?

Re: Calculation using previous row value

Thu, 18 Dec 2014 16:30:14 GMT

Actually yes for both company and item.