April 08, 2009, 11:57 AM
kfr104{OPEN} RANK same value different RANK
I have seen a couple of posts about giving consecutive rank numbers to values that are the same...
I.E.
Shoul Display:
RANK Value
1 30
2 28
3 28
4 21
I have seen other posts about this but I am unable to get my code to work...can someone help me make this modification...
JOIN
WEBFOCUS_EZG_BOOKING.WEBFOCUS_EZG_BOOKING.AGENCYID IN WEBFOCUS_EZG_BOOKING
TO MULTIPLE WEBFOCUS_REPORT_AGENCY_DATA.WEBFOCUS_REPORT_AGENCY_DATA.AGENCYID
IN WEBFOCUS_REPORT_AGENCY_DATA AS J1
END
DEFINE FILE WEBFOCUS_EZG_BOOKING
bkgDate/YYMD=HDATE (INSERTIONDATE, 'YYMD');
JAN_BKG/D12=IF bkgDate GE '2009-01-01' AND bkgDate LE '2009-01-31' THEN BOOKINGCOUNT ELSE 0;
FEB_BKG/D12=IF bkgDate GE '2009-02-02' AND bkgDate LE '2009-02-28' THEN BOOKINGCOUNT ELSE 0;
MAR_BKG/D12=IF bkgDate GE '2009-03-01' AND bkgDate LE '2009-03-31' THEN BOOKINGCOUNT ELSE 0;
APR_BKG/D12=IF bkgDate GE '2009-04-01' AND bkgDate LE '2009-04-30' THEN BOOKINGCOUNT ELSE 0;
cxlDate/YYMD=HDATE (CANCELDATE, 'YYMD');
JAN_CXL/D12=IF cxlDate GE '2009-01-01' AND cxlDate LE '2009-01-31' THEN BOOKINGCOUNT ELSE 0;
FEB_CXL/D12=IF cxlDate GE '2009-02-02' AND cxlDate LE '2009-02-28' THEN BOOKINGCOUNT ELSE 0;
MAR_CXL/D12=IF cxlDate GE '2009-03-01' AND cxlDate LE '2009-03-31' THEN BOOKINGCOUNT ELSE 0;
APR_CXL/D12=IF cxlDate GE '2009-04-01' AND cxlDate LE '2009-04-30' THEN BOOKINGCOUNT ELSE 0;
JAN_BKG_REV/D12CM=IF bkgDate GE '2009-01-01' AND bkgDate LE '2009-01-31' THEN SALESGROSS ELSE 0;
FEB_BKG_REV/D12CM=IF bkgDate GE '2009-01-01' AND bkgDate LE '2009-02-28' THEN SALESGROSS ELSE 0;
MAR_BKG_REV/D12CM=IF bkgDate GE '2009-03-01' AND bkgDate LE '2009-03-31' THEN SALESGROSS ELSE 0;
APR_BKG_REV/D12M=IF bkgDate GE '2009-04-01' AND bkgDate LE '2009-04-30' THEN SALESGROSS ELSE 0;
JAN_CXL_REV/D12CM=IF cxlDate GE '2009-01-01' AND cxlDate LE '2009-01-31' THEN SALESGROSS ELSE 0;
FEB_CXL_REV/D12CM=IF cxlDate GE '2009-02-01' AND cxlDate LE '2009-02-28' THEN SALESGROSS ELSE 0;
MAR_CXL_REV/D12CM=IF cxlDate GE '2009-03-01' AND cxlDate LE '2009-03-31' THEN SALESGROSS ELSE 0;
APR_CXL_REV/D12CM=IF cxlDate GE '2009-04-01' AND cxlDate LE '2009-04-30' THEN SALESGROSS ELSE 0;
END
TABLE FILE WEBFOCUS_EZG_BOOKING
SUM
AGENCYNAME AS 'Agency'
COMPUTE JAN_NET/D12 = JAN_BKG - JAN_CXL; AS 'JAN'
COMPUTE FEB_NET/D12 = FEB_BKG - FEB_CXL; AS 'FEB'
COMPUTE MAR_NET/D12 = MAR_BKG - MAR_CXL; AS 'MAR'
COMPUTE Q1_2009_NET/D12 = JAN_NET + FEB_NET + MAR_NET; AS 'Q1'
RANKED AS 'RANK' BY TOTAL HIGHEST 50 COMPUTE Q1_2009_NET/D12 = JAN_NET + FEB_NET + MAR_NET; NOPRINT
BY AGENCYID AS 'Tan'
HEADING
"EZG Q1 Net Bookings by Agency (Top Bookers)"
" "
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='VERDANA',
SIZE=10,
$
TYPE=DATA,
COLUMN=N12,
JUSTIFY=LEFT,
$
TYPE=DATA,
COLUMN=N15,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N16,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N17,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N14,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N12,
STYLE=BOLD,
JUSTIFY=LEFT,
$
TYPE=TITLE,
COLUMN=N15,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N16,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N17,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N14,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N13,
STYLE=BOLD,
JUSTIFY=LEFT,
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N15,
SQUEEZE=1.305556,
$
TYPE=REPORT,
COLUMN=N16,
SQUEEZE=1.430556,
$
TYPE=REPORT,
COLUMN=N17,
SQUEEZE=1.277778,
$
TYPE=REPORT,
COLUMN=N14,
SQUEEZE=1.430556,
$
TYPE=REPORT,
COLUMN=N13,
WRAP=8.000000,
$
ENDSTYLE
END
April 08, 2009, 01:53 PM
GinnyJakesDon't use the RANKED verb. Calculate a RANK field eq field+1 for every record. They you can sort by TOTAL RANK. I think there are several posts on this from the recent past.
April 08, 2009, 02:13 PM
kfr104I have tried using this function but I just am not sure what I am doing wrong...
PRINT COMPUTE RANK/I3 = RANK + 1
It is not working and I am not quite sure where to go from here...
April 08, 2009, 05:28 PM
GinnyJakesWhat happens when you add the compute right under your other computes?
What error do you get?
Have you searched the forum for other posts on RANK.
April 10, 2009, 10:30 AM
kfr104Yes, that was the first thing I did was search on for different ways to rank. I found the posting that you were talking about before I even posted anything. I tried to do what they had written but it did not seem to work...I am not sure if I wrote it correctly...
When I write that code, it gives each line a different number but not sure the reasoning behind each number it gave....ughhhhh.....
April 10, 2009, 11:24 AM
susannahquote:
PRINT COMPUTE RANK/I3 = RANK + 1
should be
PRINT COMPUTE MYRANK/I3 = LAST MYRANK + 1 ;
or
MYRANK/I3=IF SORTVARIABLE EQ LAST SORTVARIABLE THEN LAST MYRANK ELSE LAST MYRANK + 1 ;
nb: RANK is a verb, lets not use it as a variable name. the earth will tip off its axis.
April 10, 2009, 11:36 AM
GamPI think you need to do a little bit of code changing here to get it right.
I do not have your databases nor master files, but I did try to come up with something similar using the car database. It brought me to the following approach for your situation:
DEFINE FILE WEBFOCUS_EZG_BOOKING
---
---
JAN_NET/D12 = JAN_BKG - JAN_CXL; AS 'JAN'
FEB_NET/D12 = FEB_BKG - FEB_CXL; AS 'FEB'
MAR_NET/D12 = MAR_BKG - MAR_CXL; AS 'MAR'
Q1_2009_NET/D12 = JAN_NET + FEB_NET + MAR_NET; AS 'Q1'
END
TABLE FILE WEBFOCUS_EZG_BOOKING
SUM COMPUTE NR/I4 = NR + 1; AS 'RANK'
AGENCYID AS 'Tan'
AGENCYNAME AS 'Agency'
JAN_NET AS 'JAN'
FEB_NET AS 'FEB'
MAR_NET AS 'MAR'
Q1_2009_NET AS 'Q1'
BY HIGHEST 50 Q1_2009_NET NOPRINT
BY AGENCYID NOPRINT
Put the compute fields in defines, sort on those. Using RANK will assign equal numbers to equal values. But the compute nr will be calculated after all other sorting has been done and will just give each record a number that's one higher than the previous. For that to happen though, you can't use the BY TOTAL anymore, because that is done even after all the computes.
Hope this helps ...