Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Unable to find the average on an across field column
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Unable to find the average on an across field column
 Login/Join
 
Member
posted
Hi,

I have a table file and I need to do an average on the across field XYZ as shown in the code below.

TABLE FILE HOLDDP
SUM
CNT.DP
BY ID
BY NAME NOPRINT
BY REG NOPRINT
BY SUBR NOPRINT
BY RMD_YR NOPRINT
BY RMD_DT NOPRINT
BY SLS NOPRINT
BY SLD_Q NOPRINT
ACROSS XYZ
ACROSS ABC
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HOLD1
END
-RUN

When i run the code,I will get the report as shown below :
XYZ Avg(To be calculated&displayed)
ID
_________________________
AAAA 12 05 06
BBBB 05 20 15
CCCC 15 13 09

My average Calculation is something like this :
Average = ((12*5)+(05*4)+(06*3))/(12+5+6).
Finally I need to display the average value in the average column beside the XYZ field.

Could anyone tell me how to do this.Any help is greatly appreciated.

Thanks a lot!

This message has been edited. Last edited by: <Kathryn Henning>,


webfocus 769,windows 7,html
 
Posts: 24 | Registered: July 16, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
My average Calculation is something like this :
Average = ((12*5)+(05*4)+(06*3))/(12+5+6).

Can you explain what are the 5,4,3 coefficients?
What values can XYZ and ABC take?


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

 
Posts: 1954 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Hi Danny,

Thanks for your response.
I think I was not clear about how my output would look like.Below one shows how my output looks like

ID XYZ Average field
__________________________________________
AAAA 12 05 06
BBBB 05 20 15
CCCC 15 13 09

Everytime the average calculation would be something like this :
For ID - AAAA, average would be ((12*5)+(5*4)+(6*3))/(12+5+6)

For ID - BBBB,average calculation would be ((5*5)+(20*4)+(15*3))/(5+20+15) and so on..

To answer your question regarding what XYZ and ABC contains is : XYZ contains the division names and ABC contains the grouping id's within those divisions.Here in my case I have 5 such grouping id's namely G1,G2,G3,G4 and G5 which will finally gives the Count of Dp values under these grouping id's sorted by ID(AAAA,BBBB,CCCC).
Hope this helps.

Please let me know if it is still not clear.

Thanks a lot!


webfocus 769,windows 7,html
 
Posts: 24 | Registered: July 16, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Info4Pals,

If I read you right, you will always have 5 across columns and your coefficients will then be 5, 4, 3, 2, 1. Correct me if I'm wrong.
Using my assupmtion you could do the follwing:
  
TABLE FILE HOLDDP
SUM
CNT.DP
BY ID
BY NAME NOPRINT
BY REG NOPRINT
BY SUBR NOPRINT
BY RMD_YR NOPRINT
BY RMD_DT NOPRINT
BY SLS NOPRINT
BY SLD_Q NOPRINT
ACROSS XYZ
ACROSS ABC
COMPUTE AVERAGE=(C1*5 + C2*4 + C3*3 + C4*2 + C5) / (C1+C2+C3+C4+C5); 
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HOLD1
END


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

 
Posts: 1954 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Hi Danny,

I tried using your method and unfortunately the fields C1,C2,C3,C4 and C5 are not at all getting recognised.
Just as an fyi..The across field XYZ has 5 values and these values gets printed only when the code runs and Iam not quite sure as to how to take these values and do the average calculation

Please let me know how to get this resolved.

Thanks a lot!


webfocus 769,windows 7,html
 
Posts: 24 | Registered: July 16, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
info4pals,

See here, how many columns are generated by your ACROSS statements? 5 as you implied? If so what I wrote to you should work.
See a repro using the CAR file. There are only 3 values for SEATS so I use only C1, C2 and C3:
  
-* File info4pals01.fex
TABLE FILE CAR
SUM RCOST 
BY COUNTRY
BY CAR
ACROSS SEATS
COMPUTE 
AVERAGE=(C1*5 + C2*4 + C3*3) / (C1+C2+C3); 
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT *
END


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

 
Posts: 1954 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Hi Danny,

I used your method and it works for only the 1st hold file but actually Iam using 3 hold files in my report to place the resultset one beside the other.
Below is my code :
DEFINE FILE HOLDCNT
L11/A15 = 'A';
END

TABLE FILE HOLDCNT
SUM
CNT.DEPT AS 'DPTCT'
BY ST_I
BY ST_N
BY REG_I
BY SREG_I
BY RM_YR
BY RM_DATE
BY SLS_A
BY SLD_Q
BY L11 AS 'GRP'
BY SCM
WHERE L11 NE ' '
WHERE SCM EQ 'PO1' OR 'PO2' OR 'PO3' OR 'PO4' OR 'PO5'
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS SUMRY1
END
-RUN
-************************************************
TABLE FILE HOLDCNT
SUM
CNT.DEPT AS 'DPTCT'
BY ST_I
BY ST_N
BY REG_I
BY SREG_I
BY RM_YR
BY RM_DATE
BY SLS_A
BY SLD_Q
BY GP_N AS 'GRP'
BY SCM
WHERE L11 NE ' '
WHERE SCM EQ 'PO1' OR 'PO2' OR 'PO3' OR 'PO4' OR 'PO5'
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS SUMRY2
END
-RUN
************************************************TABLE FILE SUMRY1
PRINT
DPTCT ST_I ST_N REG_I SREG_I RM_YR RM_DATE SLS_A SLD_Q GRP SCM
ON TABLE HOLD AS HOLDMORE
MORE
FILE SUMRY2
END
-RUN

DEFINE FILE HOLDMORE
DPTCONT/A4=EDIT(DPTCT,'$$9999');
END

TABLE FILE HOLDMORE
SUM
DPTCONT AS 'DPTCONT1'
BY ST_I
BY ST_N
BY REG_I
BY SREG_I
BY RM_YR
BY RM_DATE
BY SLS_A
BY SLD_Q
BY GRP AS 'GRP1'
BY SCM AS 'SCM1'
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS FINALSUM
END
-RUN

DEFINE FILE HOLDPO
DEPTNEW/A4=EDIT(DEPT,'$$9999');
END

TABLE FILE HOLDPO
SUM
SCM AS 'DPTCONT1'
BY ST_I
BY ST_N
BY REG_I
BY SREG_I
BY RM_YR
BY RM_DATE
BY SLS_A
BY SLD_Q
BY GRP AS 'GRP1'
BY DEPTNEW AS 'SCM1'
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS FINALDET
END
-RUN

TABLE FILE FINALSUM
PRINT
DPTCONT1 ST_I ST_N REG_I SREG_I RM_YR RM_DATE SLS_A SLD_Q GRP1 SCM1
ON TABLE HOLD AS HOLDMORE1
MORE
FILE FINALDET
END
-RUN

TABLE FILE HOLDMORE1
SUM
DPTCONT1
BY ST_I
BY ST_N
BY REG_I
BY SREG_I
BY RM_YR
BY RM_DATE
BY SLS_A
BY SLD_Q
ACROSS GRP1
ACROSS SCM1
COMPUTE AVERAGE=(C1*5 + C2*4 + C3*3 + C4*2 +C5) / (C1+C2+C3+C4+C5);
WHERE GRP1 NE ' '
ON TABLE SET ASNAMES ON
END
-RUN
-EXIT

-*END OF THE REPORT

But when Iam trying to do an average on the last hold file on an across fiels as you suggested,I get this error message.
(FOC281)Alpha arguments in place where numeric are called for

Please help me to get this issue resolved.Thanks a lot in advance.


webfocus 769,windows 7,html
 
Posts: 24 | Registered: July 16, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
Here's a thought, for simlicity: don't use ACROSS when HOLDing files, use BY. That will simplify you understanding of what is in the HOLD files. Also, use "?FF holdfilename to ensure you see what the layout is and what the fields are.
 
Posts: 3084 | Location: Middle Tennessee | Registered: February 23, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
info4pals,

I would like to suggest a few things:
- Publish your name (at least the first one). It is nicer than a handle.
- You are very new to WebFOCUS. You should have some training. The Forum is not exactly the place for it. You can enroll in a course, find a tutor or get some online training. We do this for our customers.
- When you have a problem, like the one you are trying to solve, try a repro on the CAR file (when possible).
- When you get an error message and you display it in the forum, put the whole message; the message also gives a line number.
- And now for the nitty gritty. You were probably told that the error is on the line where you "COMPUTE AVERAGE". This means that the Cn fields are alpha, which means that DPTCONT1 is alpha, which it is because you defined it as A4.

Good luck.


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

 
Posts: 1954 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Thanks a lot Danny.Your code helped me a lot!


webfocus 769,windows 7,html
 
Posts: 24 | Registered: July 16, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Unable to find the average on an across field column

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.