I think it's due to the cold winter here that I feel completely lost with my little issue-
I have my table summing with BY and with ACROSS like that:
TABLE FILE myTable
SUM myMeasure
BY myMonth
ACROSS mySalesOrg
END
This gives me a table like that:
North South East Jan 50 50 180 Feb 10 20 30 Mar 100 50 50 ... ... ... ... Dec 80 70 40
No I want on each row and column an average value for myMeasure, like:
North South East Ave Jan 50 50 80 60 Feb 10 20 30 20 Mar 70 80 60 70 ... ... ... ... Dec 80 70 40 ...
Ave: 52 55 ... ...
Any help would be appreaciated.
Kind Regards,
StefanThis message has been edited. Last edited by: Stefan,
WF 7.6.9 PMF 5.1.3 BID 7.6.9
Win XP HTML, PDF, Excel, PowerPoint
February 08, 2010, 02:27 PM
Dan Satchell
I used the CAR file to work on this solution. There is no elegance to this, but it might work for you.
TABLE FILE CAR
SUM SEATS NOPRINT
CNT.DST.BODYTYPE NOPRINT
AND COMPUTE BODYTYPE/A12 = 'Average';
AND COMPUTE AVG_SEATS/I5 = SEATS / CNT.DST.BODYTYPE ; AS 'SEATS'
BY COUNTRY
ON TABLE HOLD AS HOLD1
END
-*
TABLE FILE CAR
SUM SEATS NOPRINT
CNT.DST.COUNTRY NOPRINT
AND COMPUTE COUNTRY/A10 = 'Average';
AND COMPUTE AVG_SEATS/I5 = SEATS / CNT.DST.COUNTRY ; AS 'SEATS'
BY BODYTYPE
ON TABLE HOLD AS HOLD2
END
-*
TABLE FILE CAR
SUM SEATS
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD AS HOLD3
END
-*
TABLE FILE HOLD1
PRINT COUNTRY
BODYTYPE
SEATS
ON TABLE HOLD AS HOLD4
MORE
FILE HOLD2
MORE
FILE HOLD3
END
-*
DEFINE FILE HOLD4
SORT_BODYTYPE/I1 = IF BODYTYPE EQ 'Average' THEN 1 ELSE 0 ;
SORT_COUNTRY/I1 = IF COUNTRY EQ 'Average' THEN 1 ELSE 0 ;
END
-*
TABLE FILE HOLD4
SUM SEATS
BY SORT_BODYTYPE NOPRINT
BY BODYTYPE AS ''
ACROSS SORT_COUNTRY NOPRINT
ACROSS COUNTRY AS ''
END
This message has been edited. Last edited by: Dan Satchell,
DEFINE FILE myFile
myMeasure2/D6=myMeasure;
END
TABLE FILE myFile
SUM
AVE.myMeasure2 NOPRINT
BY myMonth
SUM
myMeasure
BY myMonth
ACROSS mySalesOrg
COMPUTE 'Month'/D6=AVE.myMeasure2 ;
ON TABLE SUMMARIZE AVE. myMeasure
So I get nearly the output I wanted to have:
North South East Ave Jan 50 50 80 60 Feb 10 20 30 20 Mar 70 80 60 70 ... ... ... ... .... Dec 80 70 40 63
Ave: 52 55 ... ... XXX
But:
There is still one problem left: The value in the lower right corner (marked with 'XXX') isn't the average of the corresponding values, but the sum of the above column.
So I still don't have the clue.
WF 7.6.9 PMF 5.1.3 BID 7.6.9
Win XP HTML, PDF, Excel, PowerPoint
February 10, 2010, 11:42 AM
Dan Satchell
quote:
ON TABLE SUMMARIZE AVE. myMeasure
I believe the problem is with the statement above. Change it to "ON TABLE SUMMARIZE AVE." (without the myMeasure) and see if that corrects the problem. Then your 'XXX' should become the average of the numbers in that column - the same as the calculation for all of the other columns.This message has been edited. Last edited by: Dan Satchell,
WebFOCUS 7.7.05
February 10, 2010, 11:51 AM
Dan Satchell
Stefan,
Good work finding your solution on the IBI tech site. It is a much simpler solution than the one I proposed earlier. I initially tried something similar but could not figure out how to generate the averages row on the bottom of the matrix. I completely forgot about the SUMMARIZE AVE. usage. Thanks for reminding me!