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,

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,

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

Thank you, I m runnnig it based on your suggestion... let you know.

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

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

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,

OK, WILL TRY. THANKS

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

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,

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,

Wow!

Thanks.

You're welcome, Francis.

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

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

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

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,

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.

Thank you ALl! I got what I needed