Calculation using previous row value

[#1]

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?

[#2]

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

[#3]

The above function does mot reset when the item changes.

[#4]

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

[#5]

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

[preminqlik]

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; !

[#7]

Hi

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.

[preminqlik]

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;

[#9]

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

[#10]

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