Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Summing a defined field??

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Summing a defined field??
 Login/Join
 
Member
posted
I have calculated a percentage and I want to sum it up and display the sum inside a footer as a sanity check.

I have tried using sum, and I have tried creating a new define, setting it equal to 0, and then adding the percents to it, but neither of these seem to work

Here is an rough idea of how my code looks:
(note this will not run it is just an example)

Define file car
percent/D12.2 = valA/valB
totpercent/D3.2 = ??
END
table file car
on maker subfoot
"Totpercent= ??"


Dev: WF 5.2.1
Production: WF 5.2.1
Testing: WF 7.1.3
 
Posts: 26 | Registered: October 30, 2006Report This Post
Expert
posted Hide Post
Why not simply use a SUBTOTAL instead of a SUBFOOT?

DEFINE FILE CAR
PERCENT/D12.2 = DEALER_COST/RETAIL_COST;
END
TABLE FILE CAR
SUM
SALES
PERCENT
DEALER_COST
RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
ON COUNTRY SUBTOTAL
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
quote:
Originally posted by Tom Flynn:
Normally, % is calculated on a row-by-row basis with a COMPUTE, but:

ON MAKER SUBFOOT
"Total Percent =
Edit: Disable HTML


This displays the sum of ALL the percents once for every record, I only want it to sum up the percents from that specific record
(For example if there are 70 records, it will display 7,000 70 times)


Dev: WF 5.2.1
Production: WF 5.2.1
Testing: WF 7.1.3
 
Posts: 26 | Registered: October 30, 2006Report This Post
Member
posted Hide Post
quote:
Originally posted by Francis Mariani:
Why not simply use a SUBTOTAL instead of a SUBFOOT?


Well I have some other things id like to display on that same line but at least I can get a working sum of the percents this way. Maybe it will be easier to subtotal and display the other stuff on the next line..

Edit: Also subtotal as '' still prints the BY field name you are subtotaling on...I just want it to to display Totals: so subfoot gives me more control in that regard.

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


Dev: WF 5.2.1
Production: WF 5.2.1
Testing: WF 7.1.3
 
Posts: 26 | Registered: October 30, 2006Report This Post
Member
posted Hide Post
For more control and to add other details to your subfoot you can use RECAP.

-*SAMPLE USING RECAP and SUBFOOT

DEFINE FILE CAR
DFN_PCT/D12.2= RETAIL_COST / DEALER_COST;
END
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
DFN_PCT
BY COUNTRY
BY CAR
ON COUNTRY RECAP
TOTPCT/D12.2 = RETAIL_COST / DEALER_COST;
ON COUNTRY SUBFOOT
""
"Recalc of percentage TOTPCT " (use the less than sign in front of fieldname)
""
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
RIGHTGAP=0.125000,
$
END
 
Posts: 11 | Location: Information Builders Inc | Registered: April 15, 2005Report This Post
Silver Member
posted Hide Post
I am having a similar type of problem. I have a report that I want to have subtotals on three of the columns, but other columns are actually calculated percentages based on the subtotals.

For example:

PCT1 is calculated as COL1/GRANDTOTALCOL1
etc.

     COL1    COL2  PCT1   PCT2   
       15      10  37.5%  33.3%
        5      10  12.5%  33.3%
STOT:  20      20  50.0%  66.7%

       15       5  37.5%  16.7%
        2       1   5.0%   3.3%
        3       4   7.5%  13.3%
STOT:  20      10  50.0%  33.3%

GTOT:  40      30 100.0% 100.0%



This is the result of a stored procedure. What I'm doing right now is:


SQL SQLMSS
EX DB.dbo.STOREDPROC ;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS REPDATA
END

DEFINE FILE REPDATA
-* HERE I am trying to calculate total and 
-* percents
SUMCOL1 = SUM(COL1);
SUMCOL2 = SUM(COL2);
-* the next two lines are not working... 
PCTCOL1 = COL1/SUMCOL1;
PCTCOL2 = COL2/SUMCOL2;
END

TABLE FILE REPDATA
PRINT
    LabelData
    COL1/D12M
    COL2/D12M
    PCTCOL1/D4.1%
    PCTCOL2/D4.1%

BY GroupData NOPRINT
ON GroupData SUBTOTAL



I have the subtotal line and grand total lines working great but they are also summing up the percentages which is obviously not what I want. Until I get the percentages calculations working, I am just hardcoding in a value for percent to display.

So I have two questions:

1) How to calculate the percentage of a column against the grand total

2) How to print a subtotal showing THAT percentage and not the subtotal of that column value -- I've tried doing a SUBFOOT but then I run into issues with alignment that I can't work around.


WebFOCUS 7.1.5 on Win2K using SQL2000/SQL2005/ORACLE10.4
 
Posts: 47 | Registered: March 02, 2007Report This Post
Virtuoso
posted Hide Post
As a starting point, the following is a simplistic example:
TABLE FILE REPDATA
SUM COL1 COL2
PRINT
    COL1/D12M
    COL2/D12M
COMPUTE PCTCOL1/D4.1%=(C3/C1)/100;
COMPUTE PCTCOL2/D4.1%=(C4/C2)/100;
BY GroupData NOPRINT
ON GroupData RECOMPUTE

using multi verb request to SUM each column and column notation to refer to each column, where C1 refers to the SUM COL1 (i.e. the TOTAL), C2 to the SUM COL2, C3 to the PRINT COL1 and C4 to the PRINT COL2.
(Not sure this is correct 'cos having issue with it, see this topic. )

This message has been edited. Last edited by: Alan B,


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Silver Member
posted Hide Post
This works to print out the correct percent calculation, but it doesn't allow me to change the heading of the computed percent column. I was doing a "PRINT PCTCOL1 as 'heading'" -- is there another way to set the heading?

The only thing that's not showing correctly is the grand total line -- which should be showing all 100% across the columns, but instead is showing some rather nonsensical numbers.


WebFOCUS 7.1.5 on Win2K using SQL2000/SQL2005/ORACLE10.4
 
Posts: 47 | Registered: March 02, 2007Report This Post
Virtuoso
posted Hide Post
You can do AS 'heading' on a COMPUTE as well. It comes AFTER the semicolon-

You probably also want to multiply by 100 instead of dividing by 100:
COMPUTE PCTCOL1/D4.1%=(C3/C1)*100;
COMPUTE PCTCOL2/D4.1%=(C4/C2)*100;


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
Sorry, Darin, yes multiply.

It's the effect of all you guys having enjoyed the summit so much, driving me loopy over here!


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
Okay, now the official version.

DEFINE FILE Fn
CTR/I5 WITH fieldname= 1;
END
TABLE FILE Fn
SUM TOT.COL1 NOPRINT
    TOT.COL2 NOPRINT
    CTR NOPRINT
    COL1/D12M
    COL2/D12M
COMPUTE 
    PCTCOL1/D4.1% = (C4/(C1/C3))*100; 
    PCTCOL2/D4.1% = (C6/(C2/C3))*100; 
BY groupData NOPRINT
ON groupData RECOMPUTE


Based on response from IB here.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Summing a defined field??

Copyright © 1996-2020 Information Builders