Can someone please help me or point me in the right direction on figuring out how to calculate a new field 6 months back. It might sound simple but I am having a hard time figuring it out. Below is data I provided which is summed up by month. I would of tried to re-create it with using the CAR file but I dont think the CAR file has dates. I didnt really see any post with this type of calculation.

Example:

Beg of Month Proof Gallons
01/01/2010 238213.1
02/01/2010 297810.6
03/01/2010 173234.4
04/01/2010 178586.4
05/01/2010 227646.5
06/01/2010 39776.5
07/01/2010 159658.9
08/01/2010 190810.9
09/01/2010 237274.3
10/01/2010 194745.4
11/01/2010 79162.5

What I would like it to look like:

Beg of Month Proof Gallons 6 Month Worth of Proof Gallons
01/01/2010 238213.1
02/01/2010 297810.6
03/01/2010 173234.4
04/01/2010 178586.4
05/01/2010 227646.5
06/01/2010 39776.5
07/01/2010 159658.9
08/01/2010 190810.9
09/01/2010 237274.3
10/01/2010 194745.4 1228498.9 (Sum of Proof Gallons for 04/01/2010 to 10/01/2010)
11/01/2010 79162.5 1129075.0 (Sum of Proof Gallons for 05/01/2010 to 11/01/2010)

This message has been edited. Last edited by: Kerry,

"AYM function".
Just search for this function in the WF help content and you have everyting explained there.

-Hari

Hi prodrigu,

Suggestions from our internals: this online SPS page should be of help.
How to create a rolling 12 month total?

By utilizing the LAST function, a field can be created that will hold the previous record's value. Using the LAST function again on this first created value will save the value of 2 records back and so on.

Example: This report will generate a column that contains the rolling twelve month total for each Store Code (STCD). MONMx is the number of months back (ie MONM1 is the previous month value, MONM12 is the value from 12 months back). The MONM12 value is then used to subtract out of the rolling total to maintain the 12 months.

TABLE FILE GGSALES
SUM  UNITS
COMPUTE MONM1/D9= IF STCD NE LAST STCD THEN     0 ELSE LAST UNITS; NOPRINT
COMPUTE MONM2/D9= IF STCD NE LAST STCD THEN     0 ELSE LAST MONM1; NOPRINT
COMPUTE MONM3/D9= IF STCD NE LAST STCD THEN     0 ELSE LAST MONM2; NOPRINT
COMPUTE MONM4/D9= IF STCD NE LAST STCD THEN     0 ELSE LAST MONM3; NOPRINT
COMPUTE MONM5/D9= IF STCD NE LAST STCD THEN     0 ELSE LAST MONM4; NOPRINT
COMPUTE MONM6/D9= IF STCD NE LAST STCD THEN     0 ELSE LAST MONM5; NOPRINT
COMPUTE MONM7/D9= IF STCD NE LAST STCD THEN     0 ELSE LAST MONM6; NOPRINT
COMPUTE MONM8/D9= IF STCD NE LAST STCD THEN     0 ELSE LAST MONM7; NOPRINT
COMPUTE MONM9/D9= IF STCD NE LAST STCD THEN     0 ELSE LAST MONM8; NOPRINT
COMPUTE MONM10/D9=IF STCD NE LAST STCD THEN     0 ELSE LAST MONM9; NOPRINT
COMPUTE MONM11/D9=IF STCD NE LAST STCD THEN     0 ELSE LAST MONM10; NOPRINT
COMPUTE MONM12/D9=IF STCD NE LAST STCD THEN     0 ELSE LAST MONM11; NOPRINT
COMPUTE TOTVAL/D9=IF STCD NE LAST STCD THEN UNITS ELSE TOTVAL + UNITS -
                                                        MONM12;
BY REGION
BY STCD
BY DATE
ON TABLE PCHOLD AS CHKROLL FORMAT EXL2K
END

Cheers,

Kerry

Note that the method depends on there being no gaps in the sequence of periodic (monthly) records.

As a more robust alternative, one can use date arithmetic to calculate the months in the result to which the measurement of a month in the input would contribute, and pull together the component measurements by means of MATCH FILE.

Following code illustrates the method:

DEFINE FILE GGSALES
 MONTH/MTYY = DATE;
END

TABLE FILE GGSALES
SUM  UNITS  BY REGION BY STCD   BY MONTH
ON TABLE HOLD AS DATA1
END

DEFINE FILE DATA1
M0/MTYY = MONTH;
M1/MTYY  = M0+1;
M2/MTYY  = M0+2;
M3/MTYY  = M0+3;
M4/MTYY  = M0+4;
M5/MTYY  = M0+5;
M6/MTYY  = M0+6;
M7/MTYY  = M0+7;
M8/MTYY  = M0+8;
M9/MTYY  = M0+9;
M10/MTYY = M0+10;
M11/MTYY = M0+11;
END

MATCH FILE DATA1
 SUM UNITS AS U0  BY REGION BY STCD BY MONTH
RUN
FILE DATA1
 SUM UNITS AS U1  BY REGION BY STCD BY M1  AS MONTH
 AFTER MATCH HOLD OLD
RUN
FILE DATA1
 SUM UNITS AS U2  BY REGION BY STCD BY M2  AS MONTH
 AFTER MATCH HOLD OLD
RUN
FILE DATA1
 SUM UNITS AS U3  BY REGION BY STCD BY M3  AS MONTH
 AFTER MATCH HOLD OLD
RUN
FILE DATA1
 SUM UNITS AS U4  BY REGION BY STCD BY M4  AS MONTH
 AFTER MATCH HOLD OLD
RUN
FILE DATA1
 SUM UNITS AS U5  BY REGION BY STCD BY M5  AS MONTH
 AFTER MATCH HOLD OLD
RUN
FILE DATA1
 SUM UNITS AS U6  BY REGION BY STCD BY M6  AS MONTH
 AFTER MATCH HOLD OLD
RUN
FILE DATA1
 SUM UNITS AS U7  BY REGION BY STCD BY M7  AS MONTH
 AFTER MATCH HOLD OLD
RUN
FILE DATA1
 SUM UNITS AS U8  BY REGION BY STCD BY M8  AS MONTH
 AFTER MATCH HOLD OLD
RUN
FILE DATA1
 SUM UNITS AS U9  BY REGION BY STCD BY M9  AS MONTH
 AFTER MATCH HOLD OLD
RUN
FILE DATA1
 SUM UNITS AS U10 BY REGION BY STCD BY M10 AS MONTH
 AFTER MATCH HOLD OLD
RUN
FILE DATA1
 SUM UNITS AS U11 BY REGION BY STCD BY M11 AS MONTH
 AFTER MATCH HOLD AS DATA2 OLD
END

TABLE FILE DATA2
SUM U0 AS UNITS
COMPUTE ROLLING12/I8C=
  U0+U1+U2+U3+U4+U5+U6+U7+U8+U9+U10+U11;
BY REGION BY STCD BY MONTH
END

Notes:

1. Officially there is a limit to the number of stages in a single MATCH FILE request; 7.6.9 did not object to 12 stages. If necessary one can code around that restriction.

(I never understood the rationale for the limitation: in executing a MATCH FILE request, Focus only parses one step at a time, and deals with only three data streams at a time -- two sorted inputs to be merged, and the merged output. In fact, if there is a syntax error in a later stage, the earlier stages get executed before the error is noticed.)

2. The repetitive code lends itself to code generation via dialog manager.

This message has been edited. Last edited by: j.gross,

Kerry and J.Gross,

I tried both of your methods but I still was not able to come up with the calculation. I was thinking maybe it didnt work for me since you were guys had (BY REGION, BY STCD and BY DATE) and I only had (BY DATE). So below is some of the data that I am using in this report. So for every finish date there is a lot number that goes with it. The column BEG MONTH OF FINISH DATE is just taking the FINISH DATE and figuring out the beginning of that month. What I am trying to do is create a new column and for every finish date and lot number I want to be able to sum the PG's six months back. It doesnt matter if the finish dates or lot numbers are different.


FINISH DATE---LOT NUMBER----------BEG MONTH OF FINISH DATE---PG'S
03/17/2010----BBL904ZZZ-07-Z013---03/01/2010-----------------79607.5
03/19/2010----BBL904ZZZ-07-Z014---03/01/2010-----------------53611.2
04/19/2010----BBL904ZZZ-07-Z014---04/01/2010-----------------20243.4
04/14/2010----BBL904ZZZ-07-Z015---04/01/2010-----------------79457.0
04/21/2010----BBL904ZZZ-07-Z016---04/01/2010-----------------78886.0
05/11/2010----BBL904ZZZ-07-Z017---05/01/2010-----------------69746.5
05/26/2010----BBL904ZZZ-07-Z018---05/01/2010-----------------79001.7
06/14/2010----BBL904ZZZ-07-Z019---06/01/2010-----------------39776.5
05/28/2010----BBL904ZZZ-07-Z020---05/01/2010-----------------78898.3
07/02/2010----BBL904ZZZ-07-Z021---07/01/2010-----------------79915.2
07/14/2010----BBL904ZZZ-07-Z022---07/01/2010-----------------79743.7
08/03/2010----BBL904ZZZ-07-Z023---08/01/2010-----------------38823.5
08/05/2010----BBL904ZZZ-07-Z024---08/01/2010-----------------73795.9
08/18/2010----BBL904ZZZ-07-Z025---08/01/2010-----------------78191.5
09/08/2010----BBL904ZZZ-07-Z026---09/01/2010-----------------79515.4
09/15/2010----BBL904ZZZ-07-Z027---09/01/2010-----------------78537.6
09/24/2010----BBL904ZZZ-07-Z028---09/01/2010-----------------79221.3
10/27/2010----BBL904ZZZ-07-Z029---10/01/2010-----------------39553.1
10/13/2010----BBL904ZZZ-07-Z030---10/01/2010-----------------78174.6
10/19/2010----BBL904ZZZ-07-Z031---10/01/2010-----------------77017.7
11/02/2010----BBL904ZZZ-07-Z032---11/01/2010-----------------79162.5

In my first post the data you saw there is already summed up by month. The data above is not.

Let me know if you guys would like for me to post the code and I will.

Thanks!

Do you mean you want a rolling 6-month total for each distinct transaction ("finish") date?

Use McGuyver. You basically set up a cartesian product -- effectively joining each record to all the records -- and, for a given record's trans date, form the rolling total by summing the measure over all the joined-to records whose trans date falls within the appropriate 6-month window.

Yes, thats exactly want I want. Sorry for not saying it earlier.

Here's what I meant.

This produces 6-month running totals within each product-code

TABLE FILE GGORDER
SUM QUANTITY BY PRODUCT_ID BY ORDER_DATE
ON TABLE HOLD AS ORDER1
END
-* following request produces a two-segment structure, one segment per verb.
TABLE FILE ORDER1
 COUNT ENTRIES NOPRINT
  BY PRODUCT_ID AS XPROD
 SUM QUANTITY AS XQUANT
  BY PRODUCT_ID AS XPROD
  BY ORDER_DATE AS XDATE
ON TABLE SET ASNAMES ON AND HOLDLIST PRINTONLY
ON TABLE HOLD AS ORDER2 FORMAT FOCUS INDEX PRODUCT_ID
END
-* the join on product is 1-1.  
-* within each product, the descendent records are chained with XDATE as sort key
JOIN CLEAR *
JOIN PRODUCT_ID IN ORDER1 TO XPROD IN ORDER2
DEFINE FILE ORDER1
 LO_DATE/MDY = ORDER_DATE - INT(365/2);
END
-*
MATCH FILE ORDER1
 SUM QUANTITY
  BY PRODUCT_ID BY ORDER_DATE
RUN
FILE ORDER1
 SUM XQUANT
  BY PRODUCT_ID BY ORDER_DATE
 WHERE XDATE GE LO_DATE
  AND  XDATE LE ORDER_DATE;
AFTER MATCH HOLD AS ORDER3 OLD
END

TABLE FILE ORDER3
 WRITE QUANTITY AS QTY
       XQUANT AS ROLLING,6-MONTH,QTY
  ACROSS PRODUCT_ID AS PROD
  BY ORDER_DATE
  ON TABLE PCHOLD FORMAT EXL2K
END

Depending on the amount of data involved, a conditional JOIN might offer another solution.

DEFINE FILE GGORDER
 ORDER_MONTH/MY = ORDER_DATE ;
END
-*
TABLE FILE GGORDER
 SUM QUANTITY/D8
 COMPUTE BACK6_MONTHS/MY = ORDER_MONTH - 6 ;
 BY ORDER_MONTH
 BY ORDER_DATE
 ON TABLE HOLD AS HOLD1 FORMAT FOCUS INDEX ORDER_MONTH
END
-*
TABLE FILE GGORDER
 SUM QUANTITY/D8 AS 'MONTH_QTY'
 BY ORDER_MONTH
 ON TABLE HOLD AS HOLD2 FORMAT FOCUS INDEX ORDER_MONTH
END
-*
JOIN CLEAR *
JOIN LEFT_OUTER FILE HOLD1 AT ORDER_MONTH
  TO MULTIPLE   FILE HOLD2 AT ORDER_MONTH AS J1
  WHERE (HOLD2.ORDER_MONTH LT HOLD1.ORDER_MONTH)
    AND (HOLD2.ORDER_MONTH GE HOLD1.BACK6_MONTHS);
END
-*
TABLE FILE HOLD1
 SUM
  QUANTITY      AS 'Order QTY'
  MONTH_QTY     AS '6-Month QTY'
 BY ORDER_DATE  AS 'Order Date'
 BY ORDER_MONTH AS 'Order Month'
END

Dan, the solution is nice and simple!

Must be the oversight if the current month should be included:

WHERE (HOLD2.ORDER_MONTH LE HOLD1.ORDER_MONTH)
  

quote:

10/01/2010 194745.4 1228498.9 (Sum of Proof Gallons for 04/01/2010 to 10/01/2010)
11/01/2010 79162.5 1129075.0 (Sum of Proof Gallons for 05/01/2010 to 11/01/2010)

Hua,

Thanks. Prodrigu's first post might indicate that the 6-month total did not include the current month, but as you indicated the logic can easily be changed to include whatever time period is required.