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     [CLOSED] Help with my report

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Help with my report
 Login/Join
 
Member
posted
Hello there,

I am new to webfocus and to this forum. And I am wondering if you could help.

I have a matrix that displays the average # of transactions by year.

2019 2020

Branch 1 2000 3000
Branch 2 1400 2329


The average # is a compute.
How do I get the % of the average # by year? For ex. Branch 1 = 2000 / 3000

Thank you
Glen

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 15 | Registered: January 24, 2020Report This Post
Master
posted Hide Post
Welcome to the forum. I invite you to read through some of the rules and regulations here http://forums.informationbuild...1057331/m/1097029586

In order to help answer your question, can you include what version of WebFOCUS you are using? Is this a matrix chart or just a regular table?


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Member
posted Hide Post
Thanks for responding. We are currently using 8206.82. And it is a regular table.
 
Posts: 15 | Registered: January 24, 2020Report This Post
Member
posted Hide Post
Here is how the code looks like:

SUM COMPUTE Average_YTD/D12=CNT.Trans/MAX.POSTDATE_MONTH;
BY BRANCHNAME
ACROSS POSTDATE_YEAR

Thank you
Glen
 
Posts: 15 | Registered: January 24, 2020Report This Post
Member
posted Hide Post
I tackled something slightly similar (I wanted to subtotal a CNT.DST but the grain of my data was causing duplicate counts).

This is the Webfocus article I referred to when it came to subtotals/recalculations.

Manipulating Summary Values with Prefix Operators

Some calculations are supported, some are not. Maybe someone can verify which would work best for your report.

If you can used the prefix operators, great.

If not, the best way I found was to query the table and calculate the summary values before the report is created. I printed these calculated values as a sub footer (again, my situation was different but you may be abled to use a similar strategy.)

Sample code of my scenario below:

COMPUTE AGGCUSTDOWNLOAD_V.ACCOUNTS;
BY  AGGCUSTDOWNLOAD_V.CUSTOMER.BUSUNIT_DISPLAY
ON TABLE HOLD AS REPORT_SUBTOTAL FORMAT ALPHA
END

TABLE FILE AEROTEKBI/BETA2/FINANCE/AGGCUSTDOWNLOAD_V
SUM
     AGGCUSTDOWNLOAD_V.CUSTOMER.TOTALLOCALSPREADAMT
     COMPUTE AGGCUSTDOWNLOAD_V.AVGHOURSAMT;
     COMPUTE AGGCUSTDOWNLOAD_V.CONTRACTORS;
     COMPUTE AGGCUSTDOWNLOAD_V.ACCOUNTS;
BY AGGCUSTDOWNLOAD_V.BUSUNIT_SORT
BY AGGCUSTDOWNLOAD_V.BUSUNIT_DISPLAY
BY AGGCUSTDOWNLOAD_V.DELIVERYOFFICE.REGIONNAME_DEFINE
ON TABLE HOLD AS COMPANYAGG FORMAT ALPHA
END

JOIN
LEFT_OUTER COMPANYAGG.COMPANYA.BUSUNIT_DISPLAY IN COMPANYAGG TAG COMPANYA
TO MULTIPLE REPORT_SUBTOTAL.REPORT_S.BUSUNIT_DISPLAY
IN REPORT_SUBTOTAL TAG RPT_SUBT AS SUBTOTAL
END

SUM
     COMPANYA.TOTALLOCALSPREADAMT
     COMPANYA.DIFFSPREADPW NOPRINT
     COMPANYA.SPREADPW_CHANGE_ARROW_NUM
     COMPANYA.AVGHOURSAMT
     COMPANYA.AVGPWHOURSAMT NOPRINT
     COMPANYA.DIFFAVGHOURSPW NOPRINT
     COMPANYA.AVGHOURSPW_CHANGE_ARROW_NUM
     COMPANYA.CONTRACTORS
     COMPANYA.CONTRACTORSPW NOPRINT
     COMPANYA.DIFFCONTRACTORPW NOPRINT
     COMPANYA.CONTRSPW_CHANGE_ARROW_NUM
     COMPANYA.ACCOUNTS
     COMPANYA.ACCOUNTSPW NOPRINT
     COMPANYA.DIFFACCOUNTSPW NOPRINT
     COMPANYA.ACCTSPW_CHANGE_ARROW_NUM
BY  COMPANYA.BUSUNIT_SORT NOPRINT
BY  COMPANYA.BUSUNIT_DISPLAY
BY  HIGHEST COMPANYA.TOTALLOCALSPREADAMT NOPRINT
BY  COMPANYA.REGIONNAME_DEFINE AS '&&OFFICETYPE_REGION_TITLE'
ON COMPANYA.BUSUNIT_DISPLAY SUBFOOT
"Total Spread: <RPT_SUBT.TOTALLOCALSPREADAMT> | Avg Hours: <RPT_SUBT.AVGHOURSAMT> | Contr: <RPT_SUBT.CONTRACTORS> | Accts:  <RPT_SUBT.ACCOUNTS>"
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT AHTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
ENDSTYLE
END
 


Webfocus 8202 Windows
 
Posts: 20 | Registered: February 12, 2019Report This Post
Expert
posted Hide Post
It's hard to tell unless we have a clear picture of what that matrix looks like.
Does your matrix really look like this?
Year:     2019 2020

Branch 1 2000 3000
Branch 2 1400 2329

Or this?
Year Branch # MaxTrans MaxTrans
---- -------- -------- --------
2019 Branch 1     2000     3000
2019 Branch 2     1400     2329
2020 Branch 1     3000     5000
2020 Branch 2     4000     5000




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Member
posted Hide Post
Sorry I cannot figure out how to paste a screenshot, but my matrix looks like this:


Year 2019 2020
Branch Name
Branch1 123 234
Branch2 232 232

The values are average_ytd which is CNT.Accounts/MAX.PostDate.Month

CNT.Accounts is the count per month
MAX.PostDate.Month is the number of months in a year (2019 has 12, and 2020 has 5)

In the report, I have the following:
SUM
Average_YTD
By
Branch Name
Across
PostDateY

Thanks
Glen
 
Posts: 15 | Registered: January 24, 2020Report This Post
Member
posted Hide Post
Doug, it is your first example.
 
Posts: 15 | Registered: January 24, 2020Report This Post
Member
posted Hide Post
What is the problem?


FOCUS 7.1.1
OS/390 and Windows
Excel, Txt
 
Posts: 4 | Registered: April 14, 2009Report This Post
Virtuoso
posted Hide Post
quote:
the values are average_ytd which is CNT.Accounts/MAX.PostDate.Month

CNT.Accounts is the count per month
MAX.PostDate.Month is the number of months in a year (2019 has 12, and 2020 has 5)

In the report, I have the following:
SUM
Average_YTD
By
Branch Name
Across
PostDateY


Glen,
It seems to me that your procedure is in fact something like this:
  
SUM
COMPUTE Average_YTD/D6=CNT.Accounts/MAX.PostDate.Month;
BY BranchName
ACROSS PostDateY

as per your post of 19 May.

According to your request, for each BRANCH you would like to stick the ratio of the Average_YTD for the year 2019 to 2020.

I suggest you use the Cnotation: WF numbers the verb object columns, even those which are part of a COMPUTE.
Your ACROSS statement should thus be:
  
ACROSS PostDateY COMPUTE PCT_AVE/D6.2=C3 / C6;


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
Hi Danny,

Thanks for the info. For some reason i am getting an error.

ERROR AT OR NEAR LINE 19 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC224) SYNTAX ERROR: C3

(FOC009) Request failed validation, not executed.

I am pasting the code below:

-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE mmm_analytics/schemas/sl_trans_adms_sj
SUM COMPUTE Average_YTD/D12=CNT.ACCOUNT/MAX.POSTDATE_MONTH ;
BY DIMBRANCH.BranchName_Custom
ACROSS POSTDATE_YEAR_Y AS 'Post Year' COMPUTE PCT_AVE/D6.2=C3 / C6;

Seems like this should be pretty straight forward if i look at the example in https://infocenter.information...source%2Ftopic57.htm. But it does not seem to work for me.

THanks again
Glen
 
Posts: 15 | Registered: January 24, 2020Report This Post
Member
posted Hide Post
I also tried to change the column numbers but it still did not work.

I show column 1 as branchname, column 2 is the average for 2019 and column 3 is for 2020.
 
Posts: 15 | Registered: January 24, 2020Report This Post
Member
posted Hide Post
It looks like this works in App Studio but not in Info Assist.
 
Posts: 15 | Registered: January 24, 2020Report 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     [CLOSED] Help with my report

Copyright © 1996-2020 Information Builders