Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Calculating a new field for 6 months back

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Calculating a new field for 6 months back
 Login/Join
 
Platinum Member
posted
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,


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Platinum Member
posted Hide Post
"AYM function".
Just search for this function in the WF help content and you have everyting explained there.

-Hari


WF 7.7.02 on Windows 7
Teradata
HTML,PDF,EXCEL,AHTML
 
Posts: 165 | Registered: September 29, 2008Report This Post
Expert
posted Hide Post
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


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Virtuoso
posted Hide Post
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,
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
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!


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
Yes, thats exactly want I want. Sorry for not saying it earlier.


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Guru
posted Hide Post
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)
  


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Calculating a new field for 6 months back

Copyright © 1996-2020 Information Builders