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] historical data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] historical data
 Login/Join
 
Platinum Member
posted
I am interested in building a table of historical data for the purposes of trending, I have a table of data that contains gifts and pledges stored by fiscal year and giving date. currently i am running programs that on a given day give me the current years data through today and the previous years data through the calendar same day. I count distinct ID's, distinct gifts, sum the gifts and then do a compute for the AVG.

I am going to show my code below for one line of the 15 different pieces of data that I look at. I hope that you can see how I am doing what i have up to this point. I would like to expand this but the code becomes rather cumbersome and I am looking for a more efficient way, something that would loop for 365 days(don't forget leap year) and loop for 10 years, any help is appreciated, I am not a SQL wizard so WF solutions are preferred.

-* fex to set current year , last year, current date and current date a year ago today
-* MODIFIED TO ADD ADDITIONAL YEAR DATA
-* for passing to other FEXES for Friday Report caster report, EOM BOT in VSE format and EOM BOT G&C format
-* based ofn values from ATVFISC which has beginning andending dates for fiscal year
-* Generates paramters that are then passe to approximately 30 other rreports when then are merged with More
-* 2 sets of data one for current Year and on for a year ago today
-* MODIFIED DATES BECAUSE NEW VIEWS do NOT HAVE MONTH SPELLED OUT

DEFINE FILE ATVFISC
SYDATE/YYMD=&YYMD;
-*SYDATE/YYMD=20170630;

-*SYDATEPY/YYMD=DATEADD(SYDATE, 'D', -365);
-*SYDATELY/YYMD=DATEADD(SYDATE, 'D', -366);
SYFISCLY/P5=ATVFISC.ATVFISC.ATVFISC_CODE - 1;
SYFISCLY1/P5=ATVFISC.ATVFISC.ATVFISC_CODE - 2;

END
TABLE FILE ATVFISC
PRINT
ATVFISC.ATVFISC.ATVFISC_CODE


ATVFISC.ATVFISC.SYFISCLY
ATVFISC.ATVFISC.SYFISCLY1
WHERE ( SYDATE GE ATVFISC.ATVFISC.ATVFISC_START_DATE ) AND ( SYDATE LE ATVFISC.ATVFISC.ATVFISC_END_DATE);
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_FY FORMAT ALPHA
END
TABLE FILE AGBGIFT
SUM
AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE/YYMD AS 'DATE1'
AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE/M AS 'MONTH1'
COMPUTE LY_GIFT_DATE/YYMD = DATEADD(AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE, 'M', -12); AS 'DATE2'
COMPUTE LY_GIFT_DATE1/YYMD = DATEADD(AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE, 'M', -24); AS 'DATE3'
BY HIGHEST 1 AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE NOPRINT
WHERE AGBGIFT.AGBGIFT.AGBGIFT_GIFT_DATE LE '&YYMD';


ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD_YR FORMAT ALPHA
-*IF READLIMIT EQ 5
END
-RUN
-READ HOLD_YR &DATE1.A8. &MONTH1.A2. &DATE2.A8. &DATE3.A8.
-READ HOLD_FY &ATVFISC_CODE.A5. &SYFISCLY.A5. &SYFISCLY1.A5.
-SET &MONTH1X = DECODE &MONTH1('01' 'JAN' '02' 'FEB' '03' 'MAR' '04' 'APR' '05' 'MAY' '06' 'JUN' '07' 'JUL' '08' 'AUG' '09' 'SEP' '10' 'OCT' '11' 'NOV' '12' 'DEC' );
-SET &DATE1X = &DATE1;
-SET &DATE2X = &DATE2;
-SET &DATE3X = &DATE3;
-SET &FISCYRX = &ATVFISC_CODE;
-SET &FISPYRX = &SYFISCLY;
-SET &FISPYRX1 = &SYFISCLY1;


-*TYPE &DATE1X
-*TYPE &DATE2X
-*TYPE &DATE3X

-*TYPE &FISCYRX
-*TYPE &FISPYRX
-*TYPE &FISPYRX1


-*EXIT


-*component_type report
-INCLUDE IBFS:/WFC/Repository/University_Advancement/universityad/AutoYR_DT.fex
SET NODATA = .00
SET ASNAMES = ON
JOIN
LEFT_OUTER FILE AWVFRG1 AT AWVFRG1.AWVFRG1.AGBGIFT_PIDM TO UNIQUE FILE APRCATG
AT APRCATG.APRCATG.APRCATG_PIDM TAG J0 AS J0
WHERE AWVFRG1.AWVFRG1.AGBGIFT_PIDM EQ J0.APRCATG.APRCATG_PIDM;
END
DEFINE FILE AWVFRG1
ROWNM/I2=31;
SUBNAME/A50='Gifts and Pledge Payments Received';
END
TABLE FILE AWVFRG1
SUM
AWVFRG1.AWVFRG1.AGRGDES_AMT AS 'AGRGDES_AMT_CY'
AWVFRG1.AWVFRG1.CURRENT_RESTRICTED_AMT AS 'RESTRICTED_AMT_CY'
AWVFRG1.AWVFRG1.ENDOWMENT_FUNDS_AMT AS 'ENDOWMENT_AMT_CY'
AWVFRG1.AWVFRG1.UNRESTRICTED_AMT AS 'UNRESTRICTED_AMT_CY'
BY LOWEST ROWNM
BY LOWEST SUBNAME
WHERE ( AWVFRG1.AWVFRG1.AGBGIFT_FISC_CODE EQ '&FISCYRX' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_GIFT_DATE LE '&DATE1X' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_PIDM NE 577167 ) AND ( AWVFRG1.AWVFRG1.GIFT_GROUP_DESC NE 'BEQ PROP' OR 'BEQUEST' OR 'CONT/GRANT' ) AND ( J0.APRCATG.APRCATG_DONR_CODE EQ 'ALCM' OR 'ALUG' OR 'ALND' OR 'ALSF' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_CY
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/PRDODSP/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
JOIN
LEFT_OUTER FILE AWVFRG1 AT AWVFRG1.AWVFRG1.AGBGIFT_PIDM TO UNIQUE FILE APRCATG
AT APRCATG.APRCATG.APRCATG_PIDM TAG J0 AS J0
WHERE AWVFRG1.AWVFRG1.AGBGIFT_PIDM EQ J0.APRCATG.APRCATG_PIDM;
END
DEFINE FILE AWVFRG1
ROWNM/I2=31;
SUBNAME/A50='Gifts and Pledge Payments Received';
END
TABLE FILE AWVFRG1
SUM
AWVFRG1.AWVFRG1.AGRGDES_AMT AS 'AGRGDES_AMT_LY'
AWVFRG1.AWVFRG1.CURRENT_RESTRICTED_AMT AS 'RESTRICTED_AMT_LY'
AWVFRG1.AWVFRG1.ENDOWMENT_FUNDS_AMT AS 'ENDOWMENT_AMT_LY'
AWVFRG1.AWVFRG1.UNRESTRICTED_AMT AS 'UNRESTRICTED_AMT_LY'
BY LOWEST ROWNM
BY LOWEST SUBNAME
WHERE ( AWVFRG1.AWVFRG1.AGBGIFT_FISC_CODE EQ '&FISPYRX' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_GIFT_DATE LE '&DATE2X' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_PIDM NE 577167 ) AND ( AWVFRG1.AWVFRG1.GIFT_GROUP_DESC NE 'BEQ PROP' OR 'BEQUEST' OR 'CONT/GRANT' ) AND ( J0.APRCATG.APRCATG_DONR_CODE EQ 'ALCM' OR 'ALUG' OR 'ALND' OR 'ALSF' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_LY
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/PRDODSP/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
MATCH FILE H_GAP_CY
SUM H_GAP_CY.H_GAP_CY.AGRGDES_AMT_CY
BY H_GAP_CY.H_GAP_CY.ROWNM
BY H_GAP_CY.H_GAP_CY.SUBNAME
RUN
FILE H_GAP_LY
SUM H_GAP_LY.H_GAP_LY.AGRGDES_AMT_LY
BY H_GAP_LY.H_GAP_LY.ROWNM
BY H_GAP_LY.H_GAP_LY.SUBNAME
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT
HOLD.HOLD.ROWNM
HOLD.HOLD.SUBNAME
HOLD.HOLD.AGRGDES_AMT_CY
HOLD.HOLD.AGRGDES_AMT_LY
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_UG
END
SET NODATA = .00
SET ASNAMES = ON
JOIN
LEFT_OUTER FILE AWVFRG1 AT AWVFRG1.AWVFRG1.AGBGIFT_PIDM TO UNIQUE FILE APRCATG
AT APRCATG.APRCATG.APRCATG_PIDM TAG J0 AS J0
WHERE AWVFRG1.AWVFRG1.AGBGIFT_PIDM EQ J0.APRCATG.APRCATG_PIDM;
END
DEFINE FILE AWVFRG1
ROWNM/I2=35;
SUBNAME/A50='Gifts and Pledge Payments Received';
END
TABLE FILE AWVFRG1
SUM
AWVFRG1.AWVFRG1.AGRGDES_AMT AS 'AGRGDES_AMT_CY'
AWVFRG1.AWVFRG1.CURRENT_RESTRICTED_AMT AS 'RESTRICTED_AMT_CY'
AWVFRG1.AWVFRG1.ENDOWMENT_FUNDS_AMT AS 'ENDOWMENT_AMT_CY'
AWVFRG1.AWVFRG1.UNRESTRICTED_AMT AS 'UNRESTRICTED_AMT_CY'
BY LOWEST ROWNM
BY LOWEST SUBNAME
WHERE ( AWVFRG1.AWVFRG1.AGBGIFT_FISC_CODE EQ '&FISCYRX' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_GIFT_DATE LE '&DATE1X' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_PIDM NE 577167 ) AND ( AWVFRG1.AWVFRG1.GIFT_GROUP_DESC NE 'BEQ PROP' OR 'BEQUEST' OR 'CONT/GRANT' ) AND ( J0.APRCATG.APRCATG_DONR_CODE EQ 'ALCM' OR 'ALUG' OR 'ALND' OR 'ALSF' OR 'ALGR' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_CY2
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/PRDODSP/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
JOIN
LEFT_OUTER FILE AWVFRG1 AT AWVFRG1.AWVFRG1.AGBGIFT_PIDM TO UNIQUE FILE APRCATG
AT APRCATG.APRCATG.APRCATG_PIDM TAG J0 AS J0
WHERE AWVFRG1.AWVFRG1.AGBGIFT_PIDM EQ J0.APRCATG.APRCATG_PIDM;
END
DEFINE FILE AWVFRG1
ROWNM/I2=35;
SUBNAME/A50='Gifts and Pledge Payments Received';
END
TABLE FILE AWVFRG1
SUM
AWVFRG1.AWVFRG1.AGRGDES_AMT AS 'AGRGDES_AMT_LY'
AWVFRG1.AWVFRG1.CURRENT_RESTRICTED_AMT AS 'RESTRICTED_AMT_LY'
AWVFRG1.AWVFRG1.ENDOWMENT_FUNDS_AMT AS 'ENDOWMENT_AMT_LY'
AWVFRG1.AWVFRG1.UNRESTRICTED_AMT AS 'UNRESTRICTED_AMT_LY'
BY LOWEST ROWNM
BY LOWEST SUBNAME
WHERE ( AWVFRG1.AWVFRG1.AGBGIFT_FISC_CODE EQ '&FISPYRX' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_GIFT_DATE LE '&DATE2X' ) AND ( AWVFRG1.AWVFRG1.AGBGIFT_PIDM NE 577167 ) AND ( AWVFRG1.AWVFRG1.GIFT_GROUP_DESC NE 'BEQ PROP' OR 'BEQUEST' OR 'CONT/GRANT' ) AND ( J0.APRCATG.APRCATG_DONR_CODE EQ 'ALCM' OR 'ALUG' OR 'ALND' OR 'ALSF' OR 'ALGR' );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_LY2
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/PRDODSP/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
MATCH FILE H_GAP_CY2
SUM H_GAP_CY2.H_GAP_CY.AGRGDES_AMT_CY
BY H_GAP_CY2.H_GAP_CY.ROWNM
BY H_GAP_CY2.H_GAP_CY.SUBNAME
RUN
FILE H_GAP_LY2
SUM H_GAP_LY2.H_GAP_LY.AGRGDES_AMT_LY
BY H_GAP_LY2.H_GAP_LY.ROWNM
BY H_GAP_LY2.H_GAP_LY.SUBNAME
AFTER MATCH HOLD OLD-OR-NEW
END
TABLE FILE HOLD
PRINT
HOLD.HOLD.ROWNM
HOLD.HOLD.SUBNAME
HOLD.HOLD.AGRGDES_AMT_CY
HOLD.HOLD.AGRGDES_AMT_LY
ON TABLE NOTOTAL
ON TABLE HOLD AS H_GAP_ALL
END

This message has been edited. Last edited by: FP Mod Chuck,


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Report This Post
Virtuoso
posted Hide Post
Hi Geoff

I suggest using dialog manager loop coding to handle the days and years. Looking at your code it is hard to tell I really can't suggest where this code would go but hopefully that suggestion will help.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Master
posted Hide Post
Geoff,

do you really need to loop?
I this is possible to just do it in one query.

...but there is so much code to understand what you are trying to do.


TABLE FILE stuff
   SUM CNT.DST.GIFTS
       CNT.DST.PLEDGES
   BY  YEAR
   BY  MONTH
   BY DAY
ON TABLE HOLD AS H01
END

MATCH FILE H01
   SUM GIFTS AS GIFTS_CY
       PLEDGES AS PLEDGES_CY
   BY  MONTH
   BY  DAY
WHERE YEAR EQ &CY
RUN
FILE H01
   SUM GIFTS AS GIFTS_LY
       PLEDGES AS PLEDGES_LY
   BY  MONTH
   BY  DAY
WHERE YEAR EQ &LY
AFTER MATCH HOLD AS H02
END


This will give you for each month/day the data from CY and LY, without much code.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Platinum Member
posted Hide Post
Thanks to Chuck and Dave for the suggestions, Chuck I'm not sure I understand yours but I am searching for looping on focal point so perhaps I will be able to leverage that.

Dave your solution I understand pretty readily except that I am not sure that I need month. I would assume that I would need a define/compute for that correct? However if I have the date in my hold file I can get that at run time right? Also I would only need to create this file once and then I can append data from that point on for each new day.

Re: the code I stated that I was looking for a better way and both these have possibilities


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Report This Post
Master
posted Hide Post
Oh.well..

I added MONTH because DAY isn't unique ( unless it's a 1-365 counter ).
I assumed "DAY OF THE MONTH".

You just cannot use "DATE" because they differ in CY and LY, because of the year component.

Just use whatever you need.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 668 | Location: Veghel, The Netherlands | Registered: February 16, 2010Report This Post
Virtuoso
posted Hide Post
Geoff

I think Dave is right, I was overthinking this. Stick with simple if you can..


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report 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] historical data

Copyright © 1996-2020 Information Builders