[SOLVED] How to get Average of 2 columns in Subtotals
Hello,
I have a subtotal of all columns in the report, but instead of doing a subtotal on one column (AVE REV/LD), I'd like to display average of sums of 2 columns to show accurate average. Right now, the row where it shows subtotals show all totals per region...
Here's my code below:
TABLE FILE RAILDETAIL PRINT 'RAILDETAIL.RAILDETA.market' AS 'Market' 'RAILDETAIL.RAILDETA.obLoads' AS 'O/B LDS' 'RAILDETAIL.RAILDETA.ibLoads' AS 'I/B LDS' 'RAILDETAIL.RAILDETA.obTrailers' AS 'O/B TRLRS' 'RAILDETAIL.RAILDETA.ibTrailers' AS 'I/B TRLRS' 'RAILDETAIL.RAILDETA.trailerBalance' AS 'TRLR BALANCE' 'RAILDETAIL.RAILDETA.sixWkOB' AS '6 WK O/B' 'RAILDETAIL.RAILDETA.sixWkIB' AS '6 WK I/B' 'RAILDETAIL.RAILDETA.sixWkTrailerBalance' AS '6 WK TRLR BALANCE' 'RAILDETAIL.RAILDETA.totalRev/D20.2C!D' AS 'LH REV' 'RAILDETAIL.RAILDETA.avgRev/D20.2C!D' AS 'AVE REV/LD' 'RAILDETAIL.RAILDETA.avgSchedDays' AS 'AVE SCHED DAYS' 'RAILDETAIL.RAILDETA.expectedINMDLLoads' AS 'REVENUE AND COSTS,EXPECTED IMDL LDS' 'RAILDETAIL.RAILDETA.OBempties' AS 'O/B MT MOVES' 'RAILDETAIL.RAILDETA.avgRevTrlrDay/D20.2C!D' AS 'AVE REV PER TRLR/DAY' BY 'RAILDETAIL.RAILDETA.rampRegion' AS 'Rail Ramp Region'
I appreciate any help on this. Thanks!This message has been edited. Last edited by: Kerry,
Winnie
Webfocus 7.7.3
November 20, 2010, 06:02 AM
Ram Prasad E
Using FML you can get this done. Else you can do subtotal calculation in a separate hold file, then do a join or match to bring it under one file. Hope this helps.
FML i'm not familiar with, and Match is somthing i try to avoid, but maybe i'm misunderstanding the request, wouldn't bringing the two columns in, creating a new column that's the average of the sum of the two columns, then using a RECOMPUTE instead of subtotal work? I am a rookie here, but i'm interested to see what works here.
version 8202M Reporting Server on Windows Server using DB2 Connect to access data from iseries.
November 22, 2010, 01:39 AM
Dan Satchell
If you want the AVE columns recalculated instead of summed in your subtotal, try using SUMMARIZE or RECOMPUTE instead of SUBTOTAL.
WebFOCUS 7.7.05
November 29, 2010, 04:47 PM
Winnie
Hello,
Thanks for all the suggestions...the RECOMPUTE works great...Instead of returning the result on the columns from my stored procedure as data, I used an expression in order for it to work...