Hi, I've a numeric column REQ_ORD and need to calculate "NF" column on group variables PROD, CUST, PER_DT. In below example, need to pass dynamic date value '01-JUL-2013' for PER_DT column. need to consider previous 3 records(01-APR-2013, 01-MAY-2013, 01-JUN-2013) from dynamic date value('01-JUL-2013') Mentioned New calculated column "NF" with calculation description(NF description). To calculate NF for 01-Jul-2013, Avg of previous 3 records (01-APR-2013,01-MAY-2013, 01-JUN-2013) of REQ_ORD, To calculate NF for 01-Aug-2013, Avg of first 2 records(01-MAY-2013, 01-JUN-2013) of REQ_ORD and Third record(01-JUL-2013) of NF, To calculate NF for 01-Sep-2013, Avg of first record(01-Jun-2013) of REQ_ORD and last two records (01-JUL-2013, 01-AUG-2013) of NF, Always i need to start from previous three records of dynamic date value. ex: if date value 01-JUN-2013, it considers (01-MAR-2013, 01-APR-2013, 01-MAY-2013) for NF of 01-JUN-2013 Scenario has included in below and also attached. PROD CUST REQ_ORD_QTY NF PER_DT NF description 130 1214 24 01-Apr-13 130 1214 144 01-May-13 130 1214 57 01-Jun-13 130 1214 123 75 01-Jul-13 REQ_ORD_APR,REQ_ORD_MAY, REQ_ORD_JUN 130 1214 0 92 01-Aug-13 REQ_ORD_MAY,REQ_ORD_JUN, NF_JUL 130 1214 2 74.66666667 01-Sep-13 REQ_ORD_JUN,NF_JUL, NF_AUG 130 1214 3 80.55555556 01-Oct-13 NF_JUL, NF_AUG,NF_SEP 130 1214 4 82.40740741 01-Nov-13 NF_AUG,NF_SEP,NF_OCT 130 1214 5 79.20987654 01-Dec-13 NF_SEP,NF_OCT,NF_NOV 130 1214 6 01-Jan-14 NF_OCT,NF_NOV,NF_DEC 130 1214 Till 01-Apr-15 NF_JAN,NF_FEB,NF_MAR Message was edited by: Sunil Kumar
Included one more example in attached file calc.xls, want to calculate new column E(NF) and Column F is pointing calculation of column E in output tab from input data(tab: input).
want to generate number of records in output (tab: output)will be dynamamic passed date(01JUL2013) + 20 months records from 01JUL2013 + previous three records from 01JUL2013
... View more