Focal Point
[CLOSED] Rank a graph

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/127100043

January 12, 2010, 05:14 PM
lovime85
[CLOSED] Rank a graph
I need to get a top ten so i could graph their data.. is there a way to get a top based on a column values?

Thanks Big Grin

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


WebFOCUS 7.6, Windows
All output
January 12, 2010, 05:19 PM
rfbowley
BY HIGHEST 10 sortfield


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
January 12, 2010, 06:01 PM
lovime85
It is an advance graph.. I can't add many By prefixes...


WebFOCUS 7.6, Windows
All output
January 12, 2010, 06:15 PM
Dan Satchell
Using Robert's suggestion (BY HIGHEST 10 sortfield) produce a HOLD file containing only the top 10 instances. Then generate your GRAPH from the HOLD file.


WebFOCUS 7.7.05
January 13, 2010, 01:36 PM
lovime85
Can you help with that?
this is my advanced graph code..
-*INTERNAL_PROPERTIES$fieldDisplayMode=label;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$enablePreview=true;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$prefixDisplayMode=;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$GlobalRecordLimit=500;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$SampleData=false;OBJECTID=GLOBAL
GRAPH FILE F_QLTY_MEDERROR
-* Created by Advanced Graph Assistant
SUM CNT.F_QLTY_MEDERROR.F_QLTY_MEDERROR.ERROR_LEVEL
BY F_QLTY_MEDERROR.D_SPECIALTY.DESCRIPTION
ACROSS F_QLTY_MEDERROR.D_TIME.TIME_YEAR
WHERE F_QLTY_MEDERROR.D_TIME.TIME_YEAR GE 2005;
WHERE F_QLTY_MEDERROR.D_GEOGRAPHY.GEOG_REGION_DESC EQ '&GEOG_REGION_DESC.(FIND F_QLTY_MEDERROR.D_GEOGRAPHY.GEOG_REGION_DESC IN F_QLTY_MEDERROR).Geog_region_desc:.';
WHERE F_QLTY_MEDERROR.D_SERVICECENTERTYPE.SERVICECENTER_NAME EQ &SERVICECENTER_NAME.(OR(FIND F_QLTY_MEDERROR.D_SERVICECENTERTYPE.SERVICECENTER_NAME IN F_QLTY_MEDERROR)).Servicecenter_name:.;
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 985
ON GRAPH SET VAXIS 518
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 1
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setPlace(true);
setTextString(getY1Title(),"Medication Error");
setDisplay(getY1Title(),true);
setFillColor(getO1Title(),new Color(0,0,0));
setLegendPosition(2);
setTransparentFillColor(getLegendArea(),false);
setFillColor(getLegendArea(),new Color(239,239,239));
setTransparentBorderColor(getLegendArea(),false);
setTextString(getTitle(),"Total Medication Error");
setDisplay(getTitle(),true);
setTextString(getSubtitle(),"By Specialty");
setDisplay(getSubtitle(),true);
setTextString(getO1Title(),"Last 5 Years");
setDisplay(getO1Title(),true);
ENDSTYLE
ON GRAPH SET STYLE *
TYPE=DATA, ACROSSCOLUMN=N1, FOCEXEC=mederrorspecialtyreport, TARGET=_blank, $
ENDSTYLE
END


WebFOCUS 7.6, Windows
All output
January 13, 2010, 08:58 PM
Dan Satchell
TABLE FILE F_QLTY_MEDERROR
  SUM CNT.F_QLTY_MEDERROR.F_QLTY_MEDERROR.ERROR_LEVEL
   BY F_QLTY_MEDERROR.D_SPECIALTY.DESCRIPTION
   BY HIGHEST 10 F_QLTY_MEDERROR.D_TIME.TIME_YEAR
WHERE F_QLTY_MEDERROR.D_TIME.TIME_YEAR GE 2005;
WHERE F_QLTY_MEDERROR.D_GEOGRAPHY.GEOG_REGION_DESC EQ '&GEOG_REGION_DESC.(FIND F_QLTY_MEDERROR.D_GEOGRAPHY.GEOG_REGION_DESC IN F_QLTY_MEDERROR).Geog_region_desc:.';
WHERE F_QLTY_MEDERROR.D_SERVICECENTERTYPE.SERVICECENTER_NAME EQ &SERVICECENTER_NAME.(OR(FIND F_QLTY_MEDERROR.D_SERVICECENTERTYPE.SERVICECENTER_NAME IN F_QLTY_MEDERROR)).Servicecenter_name:.;
   ON TABLE HOLD
END
-*
-*INTERNAL_PROPERTIES$fieldDisplayMode=label;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$enablePreview=true;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$prefixDisplayMode=;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$GlobalRecordLimit=500;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$SampleData=false;OBJECTID=GLOBAL
GRAPH FILE HOLD
-* Created by Advanced Graph Assistant
SUM ERROR_LEVEL
BY DESCRIPTION
ACROSS TIME_YEAR
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 985
ON GRAPH SET VAXIS 518
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 1
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setPlace(true);
setTextString(getY1Title(),"Medication Error");
setDisplay(getY1Title(),true);
setFillColor(getO1Title(),new Color(0,0,0));
setLegendPosition(2);
setTransparentFillColor(getLegendArea(),false);
setFillColor(getLegendArea(),new Color(239,239,239));
setTransparentBorderColor(getLegendArea(),false);
setTextString(getTitle(),"Total Medication Error");
setDisplay(getTitle(),true);
setTextString(getSubtitle(),"By Specialty");
setDisplay(getSubtitle(),true);
setTextString(getO1Title(),"Last 5 Years");
setDisplay(getO1Title(),true);
ENDSTYLE
ON GRAPH SET STYLE *
TYPE=DATA, ACROSSCOLUMN=N1, FOCEXEC=mederrorspecialtyreport, TARGET=_blank, $
ENDSTYLE
END



WebFOCUS 7.7.05
January 14, 2010, 09:49 AM
JudyM
Will BY HIGHEST 10 stop after 10 entries, or will it include ties?

If you really want no more than 10 bars or pie slices and there could be ties, after you sort your hold file, you might want to add WHERE RECORD LIMIT EQ 10.


Judy Miller
Software Engr, Decision Support
Prism Group, Inc.
Email: judy@prism-grp.com

WebFOCUS 7.6.9
Windows XP
Output: PDF, Excel, COMT

January 14, 2010, 10:00 AM
lovime85
Hi all!
Dan Satchell.. I did as you post me..and it worked but what I need is the top 5 based on Error_Level..and when I changed it retrieves me this error:

0 NUMBER OF RECORDS IN TABLE= 10 LINES= 10
(BEFORE TOTAL TESTS)
0 NUMBER OF RECORDS IN GRAPH= 10 PLOT POINTS= 5
VERTICAL AXIS IS NOT NUMERIC..CAN'T GRAPH

What am I doing wrong?

This is the code..

TABLE FILE F_QLTY_MEDERROR
SUM
'CNT.F_QLTY_MEDERROR.F_QLTY_MEDERROR.ERROR_LEVEL'
BY 'F_QLTY_MEDERROR.D_SPECIALTY.DESCRIPTION'
BY HIGHEST 10 'F_QLTY_MEDERROR.F_QLTY_MEDERROR.ERROR_LEVEL'
WHERE F_QLTY_MEDERROR.D_TIME.TIME_YEAR GE 2005;
WHERE F_QLTY_MEDERROR.D_GEOGRAPHY.GEOG_REGION_DESC EQ '&GEOG_REGION_DESC.(FIND F_QLTY_MEDERROR.D_GEOGRAPHY.GEOG_REGION_DESC IN F_QLTY_MEDERROR).Geog_region_desc:.';
WHERE F_QLTY_MEDERROR.D_SERVICECENTERTYPE.SERVICECENTER_NAME EQ &SERVICECENTER_NAME.(OR(FIND F_QLTY_MEDERROR.D_SERVICECENTERTYPE.SERVICECENTER_NAME IN F_QLTY_MEDERROR)).Servicecenter_name:.;
ON TABLE NOTOTAL
ON TABLE HOLD
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
$
ENDSTYLE
END

GRAPH FILE HOLD
-* Created by Advanced Graph Assistant
SUM ERROR_LEVEL
BY DESCRIPTION
ACROSS ERROR_LEVEL
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 985
ON GRAPH SET VAXIS 518
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 1
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setPlace(true);
setTextString(getY1Title(),"Medication Error");
setDisplay(getY1Title(),true);
setFillColor(getO1Title(),new Color(0,0,0));
setLegendPosition(2);
setTransparentFillColor(getLegendArea(),false);
setFillColor(getLegendArea(),new Color(239,239,239));
setTransparentBorderColor(getLegendArea(),false);
setTextString(getTitle(),"Total Medication Error");
setDisplay(getTitle(),true);
setTextString(getSubtitle(),"By Specialty");
setDisplay(getSubtitle(),true);
setTextString(getO1Title(),"Last 5 Years");
setDisplay(getO1Title(),true);
ENDSTYLE
ON GRAPH SET STYLE *
TYPE=DATA, ACROSSCOLUMN=N1, FOCEXEC=mederrorspecialtyreport, TARGET=_blank, $
ENDSTYLE
END

Thank you so much Big Grin


WebFOCUS 7.6, Windows
All output
January 14, 2010, 10:13 AM
GamP
This is probably a case of field naming.
Change
TABLE FILE F_QLTY_MEDERROR
SUM 
'CNT.F_QLTY_MEDERROR.F_QLTY_MEDERROR.ERROR_LEVEL'

into
TABLE FILE F_QLTY_MEDERROR
SUM 
'CNT.F_QLTY_MEDERROR.F_QLTY_MEDERROR.ERROR_LEVEL' AS CNT_ERROR

Add ON TABLE SET ASNAMES ON to the end of the TABLE request.
And then in the graph use SUM CNT_ERROR BY .. ACROSS ..

Hope this helps ...


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
January 14, 2010, 12:41 PM
Dan Satchell
I'm no GRAPH expert but the error message seems to be saying that your Y-axis is not numeric. Your Y-axis appears to be F_QLTY_MEDERROR.D_SPECIALTY.DESCRIPTION, which I assume is a text field (alpha content).


WebFOCUS 7.7.05
January 15, 2010, 08:09 AM
Stefan
@lovime85, try
TABLE FILE F_QLTY_MEDERROR
SUM 
     'CNT.F_QLTY_MEDERROR.F_QLTY_MEDERROR.ERROR_LEVEL' AS ERRORLEVEL
BY 'F_QLTY_MEDERROR.D_SPECIALTY.DESCRIPTION' AS DESCRIPTION
RANKED AS 'TOP' BY HIGHEST 10 'F_QLTY_MEDERROR.F_QLTY_MEDERROR.ERROR_LEVEL' 
WHERE
...;
ON TABLE NOTOTAL
ON TABLE HOLD
ON TABLE SET ASNAMES ON
...
END
-RUN

GRAPH FILE HOLD
-* Created by Advanced Graph Assistant
SUM ERROR_LEVEL

ACROSS DESCRIPTION
BY ERROR_LEVEL

ON GRAPH PCHOLD FORMAT PNG...


Stefan


WF 7.6.9
PMF 5.1.3
BID 7.6.9

Win XP
HTML, PDF, Excel, PowerPoint