Hello there, I am new to webfocus and to this forum. And I a...

[Manoj Chaurasia]

Welcome to the forum. I invite you to read through some of the rules and regulations here http://forums.informationbuild1057331/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

[Manoj Chaurasia]

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

[Manoj Chaurasia]

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

Berny

[Manoj Chaurasia]

Its 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

Doug

[Manoj Chaurasia]

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

[Manoj Chaurasia]

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

[Manoj Chaurasia]

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.informationsource%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.

[Manoj Chaurasia]

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

[Toby Mills]

Hi Glen

I dont know if youve solved this already or not. Regarding your last post where you have

ACROSS POSTDATE_YEAR_Y AS Post Year

At least in the example you posted - the single quotes youre using are not the right single quotes. When I ran an example of this in 8207, my error occurred on the word Year with the same (FOC009) validation noise.

If youre using an IBI editor, watch for the syntax highlighting that might show you that the single quotes are in fact what you think they are.

Maybe its just that

Let us know how this is going and if we can assume the case is closed.

Thanks!

Toby Mills

[Toby Mills]

Guess I should have posted the difference to see if any of us can spot it in the new forum.

Glens code:

ACROSS POSTDATE_YEAR_Y AS Post Year

Normal single quotes from my 8207 console

ACROSS POSTDATE_YEAR_Y AS Post Year

Hard to spot - I know I didnt notice till I ran it and even then, the error messages arent great at telling you whats wrong.

regards

Toby

[Debra Waybright]

Toby,

I cant see a difference on the forum. Thats the kind of difference I usually spot when I copy the code into Notepad+. If you are copying code from a text editor, especially one like Word which likes to change things to look pretty, you are likely to end up with these kinds of issues. I suspect even this forum changes things to look pretty and thats why I cant see a difference, so if you copy code from here that would be something to watch for.

Good catch!

Deb

[Toby Mills]

Yeah - I found that by copying code from here into UltraEdit.

I can see the difference in the forum if I look REAL close.