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,

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.

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?

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.

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.

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,

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

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.

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.

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

Thnx. Frans! It is working!
@Gamp thnx for your help!

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.

Hi Mickey,

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

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

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

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

Hey Tom - where ya been? We are sweltering in Texas...18th day or something like that of record over 100 temps.

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.

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