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     [SOLVED] Average on Row and column

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Average on Row and column
 Login/Join
 
Gold member
posted
Hi there,

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,

Stefan

This 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
 
Posts: 69 | Location: Germany | Registered: September 21, 2009Report This Post
Virtuoso
posted Hide Post
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,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
Dan,
thanks four your proposal. I used it and a little bit of [URL=http://techsupport.information...om/sps/22302042.html ]this technique[/URL] to get it workin:

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
 
Posts: 69 | Location: Germany | Registered: September 21, 2009Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
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!


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
Dan,

  ON TABLE SUMMARIZE AVE. 
works perfect.

Thanks alot.

Kind regards,

Stefan


WF 7.6.9
PMF 5.1.3
BID 7.6.9

Win XP
HTML, PDF, Excel, PowerPoint
 
Posts: 69 | Location: Germany | Registered: September 21, 2009Report 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     [SOLVED] Average on Row and column

Copyright © 1996-2020 Information Builders