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     [CLOSED] Graph sorting on SUM..
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Graph sorting on SUM..
 Login/Join
 
Member
posted
hello,

i have a problem sorting on a SUM in a GRAPH!
i found a few old topics about sorting on a Sum in a GRAPH! but i thought maybe there is a easier way to solve this problem in WF 7.6.4!

Maybe there is a new function in WF 7.6.4 that i don't know off?
Is there a WAY to use 2 across fields with NOPRINT?

greetZ,

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


WF 7.6.4, WINDOWS XP..
Output: EXCEL, PDF, HTML.
 
Posts: 7 | Registered: November 29, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Anoir,
A way to use 2 across fields with graph using NOPRINT - to my knowledge, this is not possible.
What I do to get this to work, is to combine both acrossfields into one, store it in a hold file, and then use this hold file to create the graph, using just the one across. This method also takes care of the sorting on a SUM field, since the hold file will contain the summed values, which makes it very easy to sort on.

Hope this helps.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Hi Gamp,

thnx for your answer.. But i don't know how to combine those Accross fields in the holdfile? do you have an example code?
I want to sort the SUM from the Highest to the lowest value?


WF 7.6.4, WINDOWS XP..
Output: EXCEL, PDF, HTML.
 
Posts: 7 | Registered: November 29, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Anoir,
You haven't provided any sample of your code to work with, so I'll make something up.
Suppose you would want to across on year (A4) and on month (A2), which are two fields in your db.
And you need to do a sum for number of occurences, highest first, within some other sort field.
This would result in code like:
DEFINE FILE MYFILE
YRMND/A6 = YEAR | MONTH;
END
TABLE FILE MYFILE
SUM OCCURS
BY YRMND
BY SORTFLD
ON TABLE HOLD
END
GRAPH FILE HOLD
PRINT OCCURS
BY SORTFLD
ACROSS YRMND
ON GRAPH SET GRMERGE ON
ON GRAPH SET GRAPHEDIT SERVER
END

Hope this helps a little bit to get you going.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
The solution of GamP will work fine if you're working with years and months. But it will not work if you wan't to sort numeric fields, like profit. Anyway, it will work, but you get $1000,may|$950,january|$700,november etc.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 453 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Here is the CODE.. I want to sort on the Dealer COST from Highest to lowest!

GRAPH FILE car
SUM DEALER_COST
ACROSS MODEL
ON GRAPH SET 3D OFF
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH PCHOLD FORMAT HTML
END
-RUN

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


WF 7.6.4, WINDOWS XP..
Output: EXCEL, PDF, HTML.
 
Posts: 7 | Registered: November 29, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
This is not as easy as it seems to be.
Because of the ACROSS, you ormally would not have control over the presentation of the dcost field. It would be sorted WITHIN the scope of each individual model-value.
To overcome this, you have to give up the sorting of the model, while the across should still be in place. There is (as far as I know) only one solution for this, please correct me if I'm wrong.
Consider the next piece of code:
TABLE FILE CAR
SUM DCOST
BY TOTAL HIGHEST DCOST
BY MODEL
ON TABLE HOLD
END

DEFINE FILE HOLD
SORTNR/I2 = SORTNR + 1;
ACRFLD/A30 = FTOA(SORTNR,'(F3)','A3') || ('. ' | MODEL);
END

GRAPH FILE HOLD
SUM DEALER_COST
ACROSS ACRFLD
ON GRAPH SET 3D OFF
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH PCHOLD FORMAT PNG
END

The first table will sort by highest sum of dcost and store it in a hold file, which can then be graphed using a custom made across field.
Hope this helps....


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Hey GamP..
It works like it has to.. But i don't want to see the numbers like 1. 2. 3. 4. 5.

Is there a way to disable those? of an other way that doesn't add numbers?

Thnx!

greetz.


WF 7.6.4, WINDOWS XP..
Output: EXCEL, PDF, HTML.
 
Posts: 7 | Registered: November 29, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Because you have to have an across, it will do a sort on the across field and (in this case) only on the across field. That means that you will have to provide your own sorting algorithm, to prevent graph from applying the normal alphabetic sort (thereby disrupting the order in which you want the graph to be displayed), and this can be done in two ways:
1. by specifying your own sort order: ACROSS FIELD COLUMN VAL1 AND VAL2 AND VAL3 etc. Disadvantgae of course is that you would need to know up front what your sort values would be and how many there are.
2. the way I coded it, providing a correct alphabetic sort field. Of course, you coudl also provide a b c in stead of 1 2 3, as long as it is in alphabetic correct sort order, it does not really matter what character(s) you use.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
This will work:

-SET &ECHO = 'ALL';
SET ASNAMES = ON
SET HOLDLIST= PRINTONLY
-SET CTR = 0 ;
TABLE FILE CAR
SUM DEALER_COST
BY HIGHEST TOTAL DEALER_COST NOPRINT
BY MODEL
ON TABLE HOLD AS PREPREGRAPH
END
TABLEF FILE PREPREGRAPH
SUM
COMPUTE CTR/I1 = CTR + 1; NOPRINT
COMPUTE COMP/A60= IF CTR EQ 1 THEN '''' || MODEL || '''' ELSE ' AND ' | ('''' || MODEL || '''');
BY DEALER_COST NOPRINT
BY MODEL NOPRINT
ON TABLE SAVE AS PREGRAPH
END
-RUN
GRAPH FILE car
SUM DEALER_COST
ACROSS MODEL
COLUMNS
-INCLUDE PREGRAPH
ON GRAPH SET 3D OFF
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH PCHOLD FORMAT HTML
END
-RUN


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 453 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Thnx. Frans! It is working!
@Gamp thnx for your help!


WF 7.6.4, WINDOWS XP..
Output: EXCEL, PDF, HTML.
 
Posts: 7 | Registered: November 29, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Frans,

Nice job! I was thinking that this technique would do the trick and you proved it. This is something 'Tony A' does all the time to control sort order.

Now IBI just needs to enhance the GRAPH FILE language.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Reply With QuoteReport This Post
Guru
posted Hide Post
Hi Mickey,

Thanks, actually I've ripped this technique once from Tony A Music

But now it's clear to everybody with a CAR example!

I agree with you that this should be indeed in the GRAPH FILE language!

Frans


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 453 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I'm trying to use this technigue...but I have an additional sort field
So it would be
BY CAR
ACROSS MODEL
Trying to get it from the highest to lowest cost.
When I tried that in my real report...it did not sort higHest to lowest. What should I do different.

quote:
SET &ECHO = 'ALL';
SET ASNAMES = ON
SET HOLDLIST= PRINTONLY
-SET CTR = 0 ;
TABLE FILE CAR
SUM DEALER_COST
BY HIGHEST TOTAL DEALER_COST NOPRINT
BY MODEL
ON TABLE HOLD AS PREPREGRAPH
END
TABLEF FILE PREPREGRAPH
SUM
COMPUTE CTR/I1 = CTR + 1; NOPRINT
COMPUTE COMP/A60= IF CTR EQ 1 THEN '''' || MODEL || '''' ELSE ' AND ' | ('''' || MODEL || '''');
BY DEALER_COST NOPRINT
BY MODEL NOPRINT
ON TABLE SAVE AS PREGRAPH
END
-RUN
GRAPH FILE car
SUM DEALER_COST
ACROSS MODEL
COLUMNS
-INCLUDE PREGRAPH
ON GRAPH SET 3D OFF
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH PCHOLD FORMAT HTML
END
-RUN
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Hi Linda,

Hope all is well in Texas!

I modified this to run in MRE, and, it works fine, creating the Pareto.

  
-SET &ECHO = 'ALL';
SET ASNAMES = ON 
SET HOLDLIST= PRINTONLY 
-SET CTR = 0 ;
TABLE FILE CAR
SUM DEALER_COST
BY HIGHEST TOTAL DEALER_COST NOPRINT
BY MODEL
ON TABLE HOLD AS PREPREGRAPH
END
-RUN
TABLEF FILE PREPREGRAPH
SUM 
COMPUTE CTR/I1 = CTR + 1; NOPRINT
COMPUTE COMP/A60= IF CTR EQ 1 THEN '''' || MODEL || '''' ELSE ' AND ' | ('''' || MODEL || '''');
BY DEALER_COST NOPRINT
BY MODEL NOPRINT
ON TABLE HOLD AS PREGRAPH FORMAT ALPHA
END
-RUN
GRAPH FILE CAR
SUM DEALER_COST
ACROSS MODEL
COLUMNS
-MRNOEDIT -INCLUDE PREGRAPH
ON GRAPH SET 3D OFF
ON GRAPH SET BARNUMB OFF
ON GRAPH SET GRID ON
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET VZERO ON
ON GRAPH PCHOLD FORMAT HTML
END
-EXIT


HTH

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1973 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hey Tom - where ya been? We are sweltering in Texas...18th day or something like that of record over 100 temps. Sweating

I got the orginal I have above to work...I was asking if you threw in an extra sort field - BY CAR...in the mix...it did not.
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Reply With QuoteReport This Post
<FreSte>
posted
Hi,

I was looking for some GRAPH setting the other day and then I came across this topic.

The solution for this problem is actually in "GRAPH FILE"
I know, for this topic it's a kind of "almost 2 years too late" but maybe for future use I'm posting it.



GRAPH FILE CAR
SUM
DEALER_COST
BY HIGHEST TOTAL DEALER_COST NOPRINT
BY MODEL
ON GRAPH SET 3D OFF
ON GRAPH SET GRMERGE ON
ON GRAPH SET LOOKGRAPH VBRSTK1
END
-RUN
 
Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Graph sorting on SUM..

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