Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Bar chart sorting help
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Bar chart sorting help
 Login/Join
 
Member
posted
Hello, I've just started making charts and I could use some help.

I am working on a bar chart that sorts information

based on a selected major -> that shows how many students are in the major -> broken up by what class they are (Freshman (FR), Sophomore (SO), Junior (JR), Senior (SR), Graduate (GR)) -> Based on the term they were in (ex: Fall 2017).

I am using the class as the legend (color) for the bar chart and this is where my problem is. The sort in the graph is done alphanumerically. The bars are shown in the following order FR, GR, JR, SO, SR which is not what we want. We want them to be shown as FR, SO, JR, SR, GR.

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


WebFOCUS 8

Windows, All Outputs
 
Posts: 11 | Registered: May 02, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Add a numeric ordering field which will be hidden.
DEFINE FILE abc
ORD /I2 = DECODE CLASS (FR 1 SO 2 JR 3 SR 4 GR 5 ELSE 0);
END
GRAPH FILE abc
SUM ...
BY ORD NOPRINT
BY CLASS
END


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2161 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
This does not allow us to graph and get the legend correct. What I should have asked for was how to order the class as FR SO JR SR GR on the bar graph (which you did show us how to do) and have the legend sort that way as well.


WebFOCUS 8

Windows, All Outputs
 
Posts: 11 | Registered: May 02, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Not sure where is your issue, but I do have the legend the same order as the data
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET HTMLENCODE=ON
SET ARGRAPHENGINE=JSCHART
SET EMBEDHEADING=ON
SET GRAPHDEFAULT=OFF
DEFINE FILE CAR
ORD /I2 = DECODE COUNTRY (FRANCE 1 JAPAN 2 ENGLAND 3 ITALY 4 ELSE 5);
END
GRAPH FILE car
SUM RETAIL_COST
BY ORD NOPRINT
BY SEATS
BY COUNTRY
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET GRWIDTH 1
ON GRAPH SET UNITS PIXELS
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET LOOKGRAPH BAR
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setPieDepth(0);
setPieTilt(0);
setDepthRadius(0);
setCurveFitEquationDisplay(false);
setPlace(true);
*END
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT='WebFOCUS Report', $
TYPE=DATA, COLUMN=N1, BUCKET=x-axis, $
TYPE=DATA, COLUMN=N2, BUCKET=x-axis, $
TYPE=DATA, COLUMN=N3, BUCKET=color, $
TYPE=DATA, COLUMN=N4, BUCKET=y-axis, $
*GRAPH_SCRIPT
setReportParsingErrors(false);
setSelectionEnableMove(false);
*END
ENDSTYLE
END
-RUN

maybe by sharing your code, it's going to be much more easy to help


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2161 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
Can you simulate your concern using the EMPDATA file or an IBI Sample file to which we all have access? That way it will be easier for us to provide assistance.
 
Posts: 2976 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Try this, and see if it graphs with the sort order intact.

...
BY CLASS   COLUMNS 'FR' AND 'SO' AND 'JR' AND 'SR' AND 'GR'
... 


But note that COLUMNS will include rows in TABLE output (and their graph representation) for all the listed values, even when some have no matching incoming data.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
I've had this exact problem before. The way I've done it is by tricking WebFOCUS into making it ordered with spaces.

 DEFINE FILE abc
ORGANIZED_YEAR/A50 = IF CLASS_CODE EQ 'FR' THEN ' Freshman' ELSE IF CLASS_CODE EQ 'SO' THEN '  Sophomore' ELSE IF CLASS_CODE EQ 'JR' THEN '   Junior' ELSE IF CLASS_CODE EQ 'SR' THEN '    Senior' ELSE IF CLASS_CODE EQ 'GR' THEN '     Graduate';
END


WebFOCUS will organize by the amount of spaces.

Hope this helps!


WebFOCUS 8.2.01M on Windows 7
 
Posts: 48 | Registered: July 30, 2018Reply With QuoteReport This Post
Expert
posted Hide Post
How about DECODE instead? Althought I'm not sure that the leading spaces are significant, maybe?

DEFINE FILE abc
ORGANIZED_YEAR/A50 = DECODE CLASS_CODE ('FR' ' Freshman' 'SO' '  Sophomore' 'JR' '   Junior' 'SR' '    Senior' 'GR' THEN '     Graduate' ELSE 'Something Else');
END

quote:
DEFINE FILE abc
ORGANIZED_YEAR/A50 = IF CLASS_CODE EQ 'FR' THEN ' Freshman' ELSE IF CLASS_CODE EQ 'SO' THEN ' Sophomore' ELSE IF CLASS_CODE EQ 'JR' THEN ' Junior' ELSE IF CLASS_CODE EQ 'SR' THEN ' Senior' ELSE IF CLASS_CODE EQ 'GR' THEN ' Graduate';
END
 
Posts: 2976 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Member
posted Hide Post
We've managed to find a work around using the decode statement and a few other lines.

What we did was use the decode statement to order our information the way we wanted. After doing so, the Legend was not ordered the information the way information was on the bar graph. it. The legend still showed it's information as FR GR JR SO SR, even after the bar graph was ordered as FR SO JR SR GR.

To fix the legend problem, we used the decode to sort our class and set it to NOPRINT. Then we sorted by our newly sorted class. Afterwords we changed the following lines so the ordered class was now in the legend as we wanted it to be.

TYPE=DATA, COLUMN=N1, BUCKET=color, $
TYPE=DATA, COLUMN=N2, BUCKET=x-axis, $
TYPE=DATA, COLUMN=N3, BUCKET=x-axis, $
TYPE=DATA, COLUMN=N4, BUCKET=y-axis, $

We changed this to the N#'s that we needed them to be. Our BUCKET=color became an N2 instead of an N1. That's how we solved our legend problem.

Thank you all so much for the help. It was useful when solving the problem we had.

From,

Anguel Esperanza


WebFOCUS 8

Windows, All Outputs
 
Posts: 11 | Registered: May 02, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Bar chart sorting help

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.