Focal Point
PCT. and ACROSS and ROW-TOTAL and ACROSS-TOTAL and COLUMN-TOTAL (Here I go again!)
November 23, 2005, 12:54 PM
Francis MarianiPCT. and ACROSS and ROW-TOTAL and ACROSS-TOTAL and COLUMN-TOTAL (Here I go again!)
I want my percentage totals to make some sense.
In the example below, the report total's percentages do not make sense. The row total's percentges do are not correct.
What can I do to the program to resolve this?
Thanks,
Francis.
DEFINE FILE CAR
SALESD/D10 = SALES;
END
TABLE FILE CAR
SUM
SALESD NOPRINT
PCT.SALESD/D12.2% WITHIN CAR NOPRINT
BY COUNTRY NOPRINT
BY CAR
SUM
SALESD AS 'Sales'
PCT.SALESD/D6.2% WITHIN COUNTRY WITHIN SEATS AS '%'
BY COUNTRY NOPRINT
BY CAR
ACROSS SEATS
COMPUTE XTOT_SALESD/D15 = C1; AS 'Total'
COMPUTE PCT/D12.2 = C2; AS '%'
ON COUNTRY SUBHEAD
"COUNTRY: <COUNTRY"
ON COUNTRY SUBFOOT
" "
ON COUNTRY SUBTOTAL AS 'TOTAL'
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 23, 2005, 01:29 PM
mgrackinFrancis,
I'm not quite sure what you are trying to do. I see that WebFOCUS is summing the %'s anf therefore giving you a value greater than 100%. However, I would just not include the % field in the SUBTOTAL since it will always be 100%.
As far as the TOTAL columns go, i'm not sure what you are trying to do with the COMPUTES. Are you trying to create a total for the row?
I also trimmed down your code a bit since you have unnecessary code. What you created is a multi-verb request and it is not necessary to do that. See this code below.
DEFINE FILE CAR
SALESD/D10 = SALES;
END
TABLE FILE CAR
SUM
SALESD AS 'Sales'
PCT.SALESD/D6.2% WITHIN COUNTRY WITHIN SEATS AS '%'
BY COUNTRY NOPRINT
BY CAR
ACROSS SEATS
COMPUTE XTOT_SALESD/D15 = C1; AS 'Total'
COMPUTE PCT/D12.2 = C2; AS '%'
ON COUNTRY SUBHEAD
"COUNTRY: <COUNTRY"
ON COUNTRY SUBFOOT
" "
ON COUNTRY SUBTOTAL SALESD AS 'TOTAL'
END
Thanks!
Mickey
| FOCUS/WebFOCUS 1990 - 2011 |
November 23, 2005, 01:50 PM
Francis MarianiMickey,
Thanks for the response.
The "row total" or "across total" should be just that - a total of the row with the percentage recalculated.
For example:
England Jaguar total should be 12,000 with 100% - both total columns are showing zero.
Italy Alfa Romeo total should be 30, 200 with 100% - the total columns are showing the values of Seats=2 only - (column C1 I guess).
W Germany Audi total should be 7,800 with 8.84%
W Germany BMW total should be 80,390 with 91.16%
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 23, 2005, 02:38 PM
susannahI can tell you how I cheat...
i do my ACROSS calculations up front, in columns 1 and 2 with double verbs and noprints;
Handy when the ACROSS summary calculation is complex and not just a total.
SUM SALES NOPRINT
COMPUTE WHATEVER/... NOPRINT
BY COUNTRY
SUM SALES ACROSS CAR
AND COMPUTE TOTAL1/I8S=C1;
AND COMPUTE TOTAL2/...=C2;
BY COUNTRY
END
not elegant, but it works. and its not dependent upon the knowing the number of across items.
| In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID |
November 28, 2005, 09:01 AM
j.grossElegant, but works as advertised only when the BY and ACROSS are properly nested. For example, in --
TABLE FILE CAR
WRITE MAX.WHEELBASE
BY COUNTRY
WRITE MAX.WHEELBASE
BY COUNTRY
ACROSS BODYTYPE
COMPUTE MAX_WB/D6.1=C1;
END
-- the COMPUTE produces a row-summary column, whereas in
TABLE FILE CAR
WRITE MAX.WHEELBASE
BY COUNTRY
WRITE MAX.WHEELBASE
ACROSS BODYTYPE
BY COUNTRY
AND COMPUTE MAX_WB/D6.1=C1;
END
it produces a subfoot.
November 28, 2005, 11:54 AM
mgrackinFrancis,
The following code will get you everything EXCEPT for a proper GRANDTOTAL. If you can live without the GRANDTOTAL then this should do it for you.
DEFINE FILE CAR
SALESD/D10 = SALES;
END
-***************************************
TABLE FILE CAR
SUM SALESD NOPRINT
BY COUNTRY NOPRINT
SUM SALESD NOPRINT
BY COUNTRY NOPRINT
BY CAR
SUM SALESD AS 'Sales'
PCT.SALESD/D6.2 WITHIN COUNTRY WITHIN SEATS AS '%'
BY COUNTRY NOPRINT
BY CAR
ACROSS SEATS
COMPUTE XTOT_SALESD/D15 = C2; AS 'Total'
COMPUTE PCT/D12.2 = C2 / C1 * 100; AS '%'
-***************************************
ON COUNTRY SUBHEAD
"COUNTRY: <COUNTRY"
ON COUNTRY RECOMPUTE AS 'TOTAL'
ON TABLE NOTOTAL
END
Thanks!
Mickey
| FOCUS/WebFOCUS 1990 - 2011 |
November 28, 2005, 02:26 PM
Francis MarianiMickey, thanks for the post. I'll try to apply this to my program.
I don't understand why we have to go through pyrothecnics like this to make PCT., ACROSS totals, etc to work properly.
I'd give up a lot of bells and whistles that are added to the product every few months for some core language upgrading.
Thanks.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 28, 2005, 03:08 PM
mgrackinFrancis,
What is so difficult about your request is that you want the context of the PCT. calculation to change based on its position in the report (on a single row, in the row-total, on a subtotal line, or on a grand total line). The RECOMPUTE command does this nicely to COMPUTE fields but not to the PCT. SUBTOTAL and RECOMPUTE will only aggregate the values of the PCT. field. In later releases of WebFOCUS you can use some prefix operators in SUBFOOT lines to do something like you are trying to do but PCT. is not one of the prefix operators available.
Thanks!
Mickey
| FOCUS/WebFOCUS 1990 - 2011 |
November 28, 2005, 03:27 PM
Francis MarianiWhat I would like for Christmas, then, is an intelligent PCT prefix operator.
Cheers.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 02, 2009, 03:28 PM
MacLonghornDigging up an old thread...
I've been able to RECOMPUTE my PCT column in "ON TABLE" fashion. But I have a problem: the output is PDF and I'm using this output in a coordinated PDF. I'm using MERGE=ON which pushes the total onto a separate page.
Any ideas on a workaround?
Thanks.
Mark
WF 7.6 Windows
October 05, 2009, 02:13 PM
Francis MarianiI haven't tried this, but did you try setting PRINTPLUS to ON? I don't have a clue if this will help.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server