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,

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?

Thanks for responding. We are currently using 8206.82. And it is a regular table.

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

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

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

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

Doug, it is your first example.

What is the problem?

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;

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

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.

It looks like this works in App Studio but not in Info Assist.