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>,

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.

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