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] Two WHERE statements from same table, percentage, TOP 5, FY

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Two WHERE statements from same table, percentage, TOP 5, FY
 Login/Join
 
Member
posted
I think I can't see the forest for the trees and being new it's hard to decide which path to take. Using 7.6.11 version.

Note: FYTD is Fiscal Year To Date going from Beginning of Current FY to last date of last month. (10/1/2012-9/30/2013) PFY is Beginning of Previous FY to last date of the same month but last FY. (10/1/2011-9/30/2012). Because we are in October it just happens that my current report will include the whole year, but if this month was August, it would be last Oct to July of this year, and the previous FY would be Oct to July of the previous FY (same timeframe, one year ago)

Using the Collection_MQT table I need to get the names of the 5 Charities that collected the most amount of money this FY (TOP 5), along with the sum of the amount collected this FYTD for those TOP 5.
I then need to get the amount those same Charties collected the previous fiscal year (PFY) and figure the percentage difference between the two fiscal year's figures.
I have to add a txt file to change the Charity code to a uniform abbreviated name. (i.e code 53 would show on report as HYU)

I don't know how to do two different Where statements using the same table and calculate the percentage difference between the two amounts for this year's TOP 5 Charities.

Three columns:

Charity FYTD PFYvFYTD
HLS $52,851,341 21.67%
IDO $36,271,315 56.04%
PWQ $33,137,927 100.18%
IBV $26,259,180 8.42%
AHG $23,360,970 -5.91%

Among other attempts, I tried using HOLD just to get the name and two amounts, but the amounts show up in a single column and don't add the last FY amount in a column NEXT to the current FY (which I would have hid in my report). My next step would have been to calculate to make a visible 3rd column of the percentage.

Here's one try of just getting the amounts before even attempting to get the percentage difference:
Note: BOCFY is beginning of current fiscal year, EOPMPFY is end of previous month previous fiscal year, EOCFY is end of current fiscal year

SET BYDISPLAY=ON
SET PAGE=NOPAGE
SET NODATA='???'
SET HOLDFORMAT=ALPHA
SET HOLDLIST=PRINTONLY
SET ASNAMES=ON
SET HOLDMISS=ON
SET COMPMISS=ON


-*--Allocate abbreviation file
FILEDEF CHARABBRV DISK apid/charity_abbrev.txt (APPEND

-*--Add Define field to Collection MQT table
-*--Decode CHARABBREV field using CHARABBRV file
DEFINE FILE COLLECTION_MQT ADD
CHARABBREV/A10V=DECODE CHARITY_CODE( CHARABBRV ELSE ERR );
END

-*-- Beginning of Fiscal Year Function

DEFINE FUNCTION BOCFY(INDATE/A8)
INDTYYA/A4 = EDIT(INDATE,'9999$$$$');
INDTYY/I4 = EDIT(INDTYYA);
INDTMM/A2 = EDIT(INDATE,'$$$$99$$');
OUTDTYY/I4 = IF INDTMM LT '11' THEN INDTYY - 1 ELSE INDTYY;
BOCFY/A8 = EDIT(OUTDTYY) || '1001';
END
-RUN

-SET &DT_BOCFY = BOCFY(&YYMD);
-SET &DT_EOCFY = DATECVT(DATEADD((DATEADD((DATECVT(&DT_BOCFY,'I8YYMD','YYMD')),'M', +12)),'D', -1),'YYMD','I8YYMD');
-SET &BOPFY = DATECVT((DATEADD((DATECVT(&DT_BOCFY,'I8YYMD','YYMD')),'M', -12)),'YYMD','I8YYMD');
-SET &DT_EOPFY = DATECVT((DATEADD((DATECVT(&DT_BOCFY,'I8YYMD','YYMD')),'D', -1)),'YYMD','I8YYMD');
-SET &TODAY = EDIT(&DATEYYMD, '9999$99$99');
-SET &BEGIN_DATE = DATECVT((DATEMOV((DATECVT(&TODAY,'I8YYMD','YYMD')),'BOM')),'YYMD','I8YYMD');
-SET &PREV_DATE = DATECVT((DATEMOV((DATECVT(&BEGIN_DATE,'I8YYMD','YYMD')),'PWD')),'YYMD','I8YYMD');
-SET &PREV_EOM = DATECVT((DATEMOV((DATECVT(&PREV_DATE,'I8YYMD','YYMD')),'EOM')),'YYMD','I8YYMD');
-SET &EOPMPFY = DATECVT((DATEADD((DATECVT(&PREV_EOM,'I8YYMD','YYMD')),'M', -12)),'YYMD','I8YYMD');


-*Get TOP 5 Charity and Amount for this FYTD
TABLE FILE COLLECTION_MQT
SUM 'COLLECTION_MQT.COLLECTION_MQT.COLLECTION_AMOUNT/P16CM' AS FYTD
BY TOTAL HIGHEST 5 'COLLECTION_MQT.COLLECTION_MQT.COLLECTION_AMOUNT' NOPRINT
BY 'COLLECTION_MQT.COLLECTION_MQT.CHARITY_CODE' NOPRINT
BY CHARABBREV AS ''
WHERE ( PAYMENT_EFFECTIVE_DATE GE &DT_BOCFY ) AND ( PAYMENT_EFFECTIVE_DATE LE &PREV_EOM )
ON TABLE HOLD AS FYTD FORMAT FOCUS
END
-*Get TOP 5 Charity and Amount for PFY
TABLE FILE COLLECTION_MQT
SUM 'COLLECTION_MQT.COLLECTION_MQT.COLLECTION_AMOUNT/P16CM' AS PFYTD
BY TOTAL HIGHEST 5 'COLLECTION_MQT.COLLECTION_MQT.COLLECTION_AMOUNT' NOPRINT
BY 'COLLECTION_MQT.COLLECTION_MQT.CHARITY_CODE' NOPRINT
BY CHARABBREV AS ''
WHERE ( PAYMENT_EFFECTIVE_DATE GE &BOPFY ) AND ( PAYMENT_EFFECTIVE_DATE LE &EOPMPFY )
ON TABLE HOLD AS PVVCY FORMAT FOCUS
END

-*PRINT OUT AMOUNTS
TABLE FILE FYTD
PRINT
CHARABBREV
FYTD
MORE
FILE PVVCY
END
-RUN


Thank you in advance.

This message has been edited. Last edited by: <Kathryn Henning>,


7.6.11
Excel, PDF, HTML
 
Posts: 3 | Location: Austin, TX | Registered: September 10, 2013Report This Post
Gold member
posted Hide Post
Hi TKN,
There are multiple ways to acheive this.
But, what you are doing also would give you the right data set with one minor change.
Instead of MORE, use MATCH.
Use match phrase as OLD and you will get the top 5 charities for this year along with the previous year amount as another column.


7.7.01,windows2008 R2
 
Posts: 65 | Registered: July 28, 2011Report This Post
Member
posted Hide Post
Nice ReddyP!

MATCH and OLD worked. Now I have the 3 columns, sorted by the hightest FYTD total. I did have to change the second Total Highest 5 to Total Highest 10, otherwise I got 5 from the first file and 5 from the second but the second had one charity different than the first because some charity did better last year than this year. So getting TOP 10 will get 5 that match the first column charities. Tried taking out the 2nd Highest 5 altogether but it timed out before giving the totals.

MATCH FILE FYTDHT
PRINT
FYTD
BY CHARABBREV
RUN
FILE PVVCYHT
PRINT
PFYTD
BY CHARABBREV
AFTER MATCH HOLD OLD
END
TABLE FILE HOLD
PRINT
'HOLD.HOLD.CHARABBREV'
'HOLD.HOLD.FYTD'
'HOLD.HOLD.PFYTD'
BY HIGHEST 'HOLD.HOLD.FYTD' NOPRINT
ON TABLE NOTOTAL
END


Now I have to learn about COMPUTE, I guess, to get the percentage of those two amount columns.
Thanks


7.6.11
Excel, PDF, HTML
 
Posts: 3 | Location: Austin, TX | Registered: September 10, 2013Report 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] Two WHERE statements from same table, percentage, TOP 5, FY

Copyright © 1996-2020 Information Builders