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     [SOLVED] Year to Date question

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Year to Date question
 Login/Join
 
Platinum Member
posted
I'm working on a Year to Date report that isn't working out. I'm getting output, but it's empty. I feel it's something simple that I'm overlooking, but I'm not sure what it is. Can I get a second pair of eyes to see what I'm doing wrong? I get the Months output, but now rows. I should be expecting "FYTD 2013", "FYTD 2014", and "FYTD 2015". Thanks a bunch!! Here's the code:

-* File: IBFS:/Dev/WFC/Repository/TonyTest/PFSKPIs/Report1.fex Created by WebFOCUS AppStudio
-TYPE CONTENT FOLDER:  TestTony   FOCEXEC: gross_chrgs_fytd.fex
-SET ECHO = ALL

-?&
-DEFAULTH &WFFMT='HTML'

SET PRINTPLUS = ON
-INCLUDE IBFS:/WFC/Repository/TonyTest/PFSKPIs/getdate_vars.fex


-DEFAULTH &FM=' '
-READFILE PFSRPTPD
-SET &CFM = '&FM.EVAL';
DEFINE FILE PFSAR ADD
HOLD_BLANK/A1=' ';
END
TABLE FILE PFSAR
PRINT
  COMPUTE CNTR/I2=LAST CNTR + 1; NOPRINT
  COMPUTE HOLD_MONTH/P2= DECODE CNTR(1 01
  									 2 02
									 3 03
									 4 04
									 5 05
									 6 06
									 7 07
									 8 08
									 9 09
									 10 10
									 11 11
									 ELSE 12);
BY HOLD_BLANK
BY FM NOPRINT
WHERE RECORDLIMIT EQ 12
ON TABLE HOLD AS MACGYVER FORMAT FOCUS INDEX HOLD_BLANK
END

JOIN BLANK WITH FM IN PFSAR TO ALL HOLD_BLANK IN MACGYVER AS J1
DEFINE FILE PFSAR
BLANK/A1 = ' ';
END


TABLE FILE PFSAR
SUM

	     COMPUTE YTD_GRCOL/D10CB = IF FM EQ HOLD_MONTH THEN PFSAR.PFSAR.CHARGES ELSE 0;
BY  PFSAR.PFSAR.PSTPDFY
BY  PFSAR.PFSAR.FM
BY  PFSAR.PFSAR.FYMTH
BY  HOLD_MONTH



WHERE ( PFSAR.PFSAR.PSTPDFY GE (&PFSRPTPD.(FIND PFSRPTPD.PFSRPTPD.VPSTPDFY IN PFSRPTPD).VPSTPDFY:.) -2 
       AND PFSAR.PFSAR.POSTPERIOD LE (&PFSRPTPD.(FIND PFSRPTPD.PFSRPTD.VPOSTPER IN PFSRPTPD).VPOSTPER:.));



ON TABLE NOTOTAL
ON TABLE HOLD AS H1

END




TABLE FILE H1
SUM YTD_GRCOL
 	 COMPUTE YTD/D10CB MISSING ON  = IF MAX.HOLD_MONTH EQ 1 THEN YTD_GRCOL ELSE
	                    IF MAX.PSTPDFY EQ &P2FY THEN YTD_GRCOL + LAST YTD ELSE
	 					IF MAX.PSTPDFY EQ &PFY THEN YTD_GRCOL + LAST YTD ELSE
						IF MAX.PSTPDFY EQ &CFY AND MAX.HOLD_MONTH LE &CFM THEN YTD_GRCOL + LAST YTD
						ELSE 0;

BY  PSTPDFY
BY  HOLD_MONTH

ON TABLE HOLD AS H1_TREND

END

DEFINE FILE H1_TREND
FYMTH/A3=DECODE HOLD_MONTH(01 'Oct'
                           02 'Nov'
						   03 'Dec'
						   04 'Jan'
						   05 'Feb'
						   06 'Mar'
						   07 'Apr'
						   08 'May'
						   09 'Jun'
						   10 'Jul'
						   11 'Aug'
						   12 'Sep'
						   ELSE 'ERR');
END
-RUN



TABLE FILE H1_TREND
SUM
 MAX.YTD AS ''

ACROSS LOWEST FYMTH AS '' COLUMNS 'Oct' AND 'Nov' AND 'Dec' AND 'Jan' AND 'Feb' AND 'Mar' AND 'Apr' AND 'May' AND 'Jun' AND 'Jul' AND 'Aug' AND 'Sep'
FOR
     PSTPDFY
'&P2FY.EVAL' AS 'FYTD &P2FY.EVAL' LABEL R1 OVER
'&PFY.EVAL' AS 'FYTD &PFY.EVAL' LABEL R2 OVER
'&CFY.EVAL' AS 'FYTD &CFY.EVAL' LABEL R3 OVER
RECAP Variance/D10.1%B=IF R3 NE 0 THEN  (((R3 - R2) / R2)  * 100) ELSE 0;

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/WFC/Repository/Branding/np_gphc_theme.sty,


Thanks again!!
Pondog

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


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Platinum Member
posted Hide Post
UPDATE: SOLVED

Ok. I found my problem. It appears that for some reason I'm not pulling the correct field in my "FIND" statement.
WHERE ( PFSAR.PFSAR.PSTPDFY GE ((&PFSRPTPD.(FIND PFSRPTPD.PFSRPTPD.VPSTPDFY IN PFSRPTPD).VPSTPDFY:.) -2)
       AND PFSAR.PFSAR.POSTPERIOD LE (&PFSRPTPD.(FIND PFSRPTPD.PFSRPTPD.VPOSTPER IN PFSRPTPD).VPOSTPER:.));


The second field "VPOSTPER" should return "201509", however it's returning the same field as "VPSTPDFY" which is "2015"

After much research I found that I needed to change the names on my variables. The following code was/is successful:

WHERE ( PFSAR.PFSAR.PSTPDFY GE ((&VPSTPDFY.(FIND PFSRPTPD.PFSRPTPD.VPSTPDFY IN PFSRPTPD).VPSTPDFY:.)-2)
AND   PFSAR.PFSAR.POSTPERIOD LE (&VPOSTPER.(FIND PFSRPTPD.PFSRPTPD.VPOSTPER IN PFSRPTPD).VPOSTPER:.));


Yeah! Nothing like a minor victory. Thanks for a "searchable" forum.

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


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report 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     [SOLVED] Year to Date question

Copyright © 1996-2020 Information Builders