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     Average in the same line where total is - SOLVED

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Average in the same line where total is - SOLVED
 Login/Join
 
Gold member
posted
Hello!
have a report in Exc where I display totals and now I need to calculate average value for some columns. By Digging here, I could not find a good sample or disscussion, PLease advise!

%OT column need to be calculated as AVE , which in this sample is 6.77%

Group Total Lines Total Cases Sales$ %OT
A 237403 758444 $29,016,783.18 7.28%
A 142648 609691 $24,208,955.37 12.33%
B 112977 432301 $13,906,708.61 .43%
C 65870 103018 $9,860,405.87 7.04%
Total 558898 1903454 $76,992,853.03 6.77

my code:

DEFINE FILE FINAL1
DUMMY_FIELD/A8='';
END

SET BYDISPLAY=ON
TABLE FILE FINAL1
HEADING
"Monthly Metrics Cases "
"Run Date: &DATEMtrDYY"
"Period:&LAST_PERIOD, Dates: &START_DT_OF_PERIOD - &LAST_DT_OF_PERIOD"
""
PRINT
GROUP AS 'Group'
TOTAL_LINES AS 'Total Lines'
TOTAL_CASES/P10 AS 'Total Cases'
SALES/P12.2M AS 'Sales$'
PRC_OT AS '%OT'
BY DUMMY_FIELD AS ''
BY GROUP NOPRINT
ON TABLE COLUMN-TOTAL AS 'Total' TOTAL_LINES TOTAL_CASES SALES
ON TABLE PCHOLD FORMAT EXL2K
FOOTING
" "
ON TABLE SET STYLE *
UNITS=IN,
LEFTMARGIN=0.1,
RIGHTMARGIN=0.1,
TOPMARGIN=0.1,
BOTTOMMARGIN=0.1,
SQUEEZE=ON,
PAGESIZE=LETTER,
ORIENTATION=LANDSCAPE,$
TYPE=REPORT,FONT=TIMES NEW ROMAN,SIZE=7,GRID=OFF,$
TYPE=DATA,JUSTIFY=CENTER,SIZE=7,$
ENDSTYLE
END

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


Valeriya

WebFOCUS 764 Servlet - MRE/Dashboard/Self Service/ReportCaster - Windows 2000
 
Posts: 68 | Location: Chicago | Registered: August 23, 2007Report This Post
Virtuoso
posted Hide Post
The easiest solution may be to replace the COLUMN-TOTAL with a SUBFOOT and calculate your final average %.

DEFINE FILE CAR
DUMMY_FIELD/A8='';
END
SET BYDISPLAY=ON
TABLE FILE CAR
HEADING
"Monthly Metrics Cases "
"Run Date: &DATEMtrDYY"
"Period:&LAST_PERIOD, Dates: &START_DT_OF_PERIOD - &LAST_DT_OF_PERIOD"
""
SUM
GROUP AS 'Group'
TOTAL_LINES AS 'Total Lines'
TOTAL_CASES/P10 AS 'Total Cases'
SALES/P12.2M AS 'Sales$'
PRC_OT AS '%OT'
COMPUTE CNT_ROWS/I5 = CNT_ROWS + 1 ; NOPRINT
COMPUTE AVG_PRC_OT/D6.2 = TOT.PRC_OT / CNT_ROWS ; NOPRINT
BY DUMMY_FIELD AS ''
BY GROUP NOPRINT
ON DUMMY SUBFOOT
"<+0> <+0> <TOT.TOTAL_LINES<TOT.TOTAL_CASES<TOT.SALES<AVG_PRC_OT " 
ON TABLE PCHOLD FORMAT EXL2K 
FOOTING
" "
ON TABLE SET STYLE *
UNITS=IN,
LEFTMARGIN=0.1,
RIGHTMARGIN=0.1,
TOPMARGIN=0.1,
BOTTOMMARGIN=0.1,
SQUEEZE=ON,
PAGESIZE=LETTER,
ORIENTATION=LANDSCAPE,$
TYPE=REPORT,FONT=TIMES NEW ROMAN,SIZE=7,GRID=OFF,$
TYPE=DATA,JUSTIFY=CENTER,SIZE=7,$
TYPE=SUBFOOT,HEADALIGN=BODY, $
ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
Would a regular AVE prefix in the total line help there?

TABLE FILE CAR
HEADING
"blah"
SUM
        GROUP AS 'Group'
        TOTAL_LINES AS 'Total Lines'
        TOTAL_CASES/P10 AS 'Total Cases'
        SALES/P12.2M AS 'Sales$'
        PRC_OT AS '%OT'
BY DUMMY_FIELD AS ''
BY COUNTRY NOPRINT
ON DUMMY RECOMPUTE AS 'Total'  SUM.TOTAL_LINES  SUM.TOTAL_CASES  SUM.SALES  AVE.PRC_OT
ON TABLE PCHOLD FORMAT EXL2K 
FOOTING
" "
ON TABLE SET STYLE *
$ blah
ENDSTYLE
END



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Gold member
posted Hide Post
Thank you, I m runnnig it based on your suggestion... let you know.


Valeriya

WebFOCUS 764 Servlet - MRE/Dashboard/Self Service/ReportCaster - Windows 2000
 
Posts: 68 | Location: Chicago | Registered: August 23, 2007Report This Post
Gold member
posted Hide Post
I ve been getting this error:
0 ERROR AT OR NEAR LINE 2399 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC002) A WORD IS NOT RECOGNIZED: AVE.PRC_OT BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

jUST TO TRY... I use only one field:

ON DUMMY RECOMPUTE AVE.PRC_OT


Valeriya

WebFOCUS 764 Servlet - MRE/Dashboard/Self Service/ReportCaster - Windows 2000
 
Posts: 68 | Location: Chicago | Registered: August 23, 2007Report This Post
Virtuoso
posted Hide Post
Oh, I made a mistake in the code I posted! Sorry about that.

There must be a space separating AVE. from the field name:

ON DUMMY RECOMPUTE AVE. PRC_OT



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
Something that is bugging me is .. do you really want to have an average of percentages?

Wouldn't you rather have some kind of an weighted average in which you'd calculate a final percentage based on aggregated values of whatever numerator / denominator you use?

See code below:

TABLE FILE CAR.DEALER_COST
PRINT 
     SEATS AS 'Count'
     DEALER_COST AS 'Total,Cost'
     RETAIL_COST AS 'Total,Sales'
     COMPUTE PCT_RD/D6.2% = (RETAIL_COST / DEALER_COST - 1) * 100;
BY COUNTRY
     
ON COUNTRY RECOMPUTE
ON TABLE NOTOTAL
END

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Gold member
posted Hide Post
OK, WILL TRY. THANKS


Valeriya

WebFOCUS 764 Servlet - MRE/Dashboard/Self Service/ReportCaster - Windows 2000
 
Posts: 68 | Location: Chicago | Registered: August 23, 2007Report This Post
Expert
posted Hide Post
quote:
There must be a space separating AVE. from the field name

Neftali, why is that? I've never heard of this, sound like a bug???


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
Virtuoso
posted Hide Post
No, it is designed that way.

I'll try to find the documentation entry and will post it here for reference!

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
Document: Creating Reports With WebFOCUS Language 761 (DN4500804.1209)
Section: Manipulating Summary Values With Prefix Operators
Page: 280 (PDF version)

quote:

pref. field1 [field2 ... fieldn] [pref2. fieldm ...]
The first prefix operator is applied to field1 through fieldn. The second prefix operator
is applied to fieldm. Only the fields specified are populated with values on the summary
row. Each prefix operator must be separated by a blank space from the following field
name
.

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
Wow!

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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
You're welcome, Francis. Wink



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Gold member
posted Hide Post
quote:
Originally posted by Dan Satchell:
The easiest solution may be to replace the COLUMN-TOTAL with a SUBFOOT and calculate your final average %.

DEFINE FILE CAR
DUMMY_FIELD/A8='';
END
SET BYDISPLAY=ON
TABLE FILE CAR
HEADING
"Monthly Metrics Cases "
"Run Date: &DATEMtrDYY"
"Period:&LAST_PERIOD, Dates: &START_DT_OF_PERIOD - &LAST_DT_OF_PERIOD"
""
SUM
GROUP AS 'Group'
TOTAL_LINES AS 'Total Lines'
TOTAL_CASES/P10 AS 'Total Cases'
SALES/P12.2M AS 'Sales$'
PRC_OT AS '%OT'
COMPUTE CNT_ROWS/I5 = CNT_ROWS + 1 ; NOPRINT
COMPUTE AVG_PRC_OT/D6.2 = TOT.PRC_OT / CNT_ROWS ; NOPRINT
BY DUMMY_FIELD AS ''
BY GROUP NOPRINT
ON DUMMY SUBFOOT
"<+0> <+0> <TOT.TOTAL_LINES<TOT.TOTAL_CASES<TOT.SALES<AVG_PRC_OT " 
ON TABLE PCHOLD FORMAT EXL2K 
FOOTING
" "
ON TABLE SET STYLE *
UNITS=IN,
LEFTMARGIN=0.1,
RIGHTMARGIN=0.1,
TOPMARGIN=0.1,
BOTTOMMARGIN=0.1,
SQUEEZE=ON,
PAGESIZE=LETTER,
ORIENTATION=LANDSCAPE,$
TYPE=REPORT,FONT=TIMES NEW ROMAN,SIZE=7,GRID=OFF,$
TYPE=DATA,JUSTIFY=CENTER,SIZE=7,$
TYPE=SUBFOOT,HEADALIGN=BODY, $
ENDSTYLE
END


Guru,
this line: "<+0> <+0> I used your advise but I see how numbers display on that line! One after the other.
How can I lay out them so total_lines shows in the cell under 'Total Lines' for example? Is there any way to have a better display?


Valeriya

WebFOCUS 764 Servlet - MRE/Dashboard/Self Service/ReportCaster - Windows 2000
 
Posts: 68 | Location: Chicago | Registered: August 23, 2007Report This Post
Gold member
posted Hide Post
Guru,
what does it mean?
"<+0> <+0> My numbers seem to be fine, I likes the result but the way these numbers display - do not like it! Is there a way to lay thwm out acording the fields? For example, TOT.TOTAL_CASES should display in the cell under 'Total Cases' ect.?
It's Excell report.
Thank you


Valeriya

WebFOCUS 764 Servlet - MRE/Dashboard/Self Service/ReportCaster - Windows 2000
 
Posts: 68 | Location: Chicago | Registered: August 23, 2007Report This Post
Gold member
posted Hide Post
Ive been trying to reply to Guru's post and having problems.. Anyway,
Thank you, I used that logic, but numbers display way off! I mean They are correct numbers but cells where they display are off. Is there a way to print the values in that subfoot line under coordinated fields?
here is my code:
table file
print....
COMPUTE CAL2/P10.3%= LABOR/SALES; AS 'Labor% Sales'
MODF_LB_PCT_SALE AS 'Modified L%S'
OVRL_SLS_PR_LN/P10.2M AS 'Overall Sales$/Line'
PRCNT_RPCK_LNS AS '%Repack Lines'
CPR AS 'Cases/Hour'
COMPUTE CNT_ROWS/I5 = CNT_ROWS + 1 ; NOPRINT
COMPUTE AVG_CASE_PER_HR/D20.2 = TOT.CPR / CNT_ROWS ; NOPRINT
CASES_PR_LN AS 'Case/Line'
COMPUTE AVG_CASES_PR_LN/D20.2 = TOT.CASES_PR_LN / CNT_ROWS ; NOPRINT
BY DUMMY_FIELD AS ''
BY GROUP NOPRINT
WHERE NOT PLANT IN ('B01','B11','B44','B71','B79')
ON DUMMY_FIELD SUBFOOT
"<+0> <+0> ON TABLE PCHOLD FORMAT EXL2K


Valeriya

WebFOCUS 764 Servlet - MRE/Dashboard/Self Service/ReportCaster - Windows 2000
 
Posts: 68 | Location: Chicago | Registered: August 23, 2007Report This Post
Virtuoso
posted Hide Post
Valeriya,

Those nice <+0> symbols are called sportmarkers and are used by WebFOCUS to properly align and/or position HEADING/FOOTING/SUBFOOT/SUBHEAD elements along with data columns in the body of your report.

You can read all about them in the Creating Reports with WebFOCUS Language 7.6 manual (DN# 4500804.1209) which is available in IBI's technical support website.

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
As Neftali said, the <+0> symbols are spot markers that work in conjunction with the stylesheet statement TYPE=SUBFOOT,HEADALIGN=BODY to position the SUBFOOT fields underneath the correct columns. You may need to use fewer or more spot markers to correctly align your footing.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
Thank you ALl! I got what I needed


Valeriya

WebFOCUS 764 Servlet - MRE/Dashboard/Self Service/ReportCaster - Windows 2000
 
Posts: 68 | Location: Chicago | Registered: August 23, 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     Average in the same line where total is - SOLVED

Copyright © 1996-2020 Information Builders