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.
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
GlenThis 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;
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
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
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:
Doug, it is your first example.
What is the problem?
OS/390 and Windows
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;
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
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 ;
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.
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.
|Powered by Social Strata|