Hi , I have faced this problem please help me on this problem . I have last 5 weeks customers data, i want to compare recent week data to last 4 weeks data. The output data set has only one row for each customer and their last 4 weeks metrics. The data looks like: Data Have; input Date mmddyy10. Cust_Num$ Amount Days Index ; cards; 02-03-2015 ABC 150 7 2 01-27-2015 ABC 100 5 2 01-20-2015 ABC 100 5 2 01-13-2015 ABC 100 5 2 01-06-2015 ABC 100 5 2 02-03-2015 DEF 150 5 3 01-27-2015 DEF 150 6 3 01-20-2015 DEF 150 6 3 01-06-2015 DEF 100 6 3 run; I want Output dataset Like this: Date Cust_Num Amount Days Index Last 4 weeks Amount Last 4 weeks Days Last 4 weeks Index 02-03-2015 ABC 150 7 2 400 5 3 02-03-2015 DEF 150 5 3 400 6 3 Actually i want to compare recent date with last 4 wees data. Here feb 2nd is the recent date for ABC customer it has 4 weeks data So it will take 4 weeks and DEF has only 3 weeks data it will take 3 weeks data only. Last 4 wees amount= Sum of last weeks amount Last 4 weeks Days= SumProcduct(Amount,Days)/Last 4 weeks amount Last 4 weeks Index=Sumproduct(Amount,Days)/Last 4 weeks amount Sumproduct will actually multiplies the both columns in Excel. I want to multiply amount and days then devide that amount with the total 4 wees amount because i want weighted days same for index. Thanks in advance.
... View more