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>,

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?

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!

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

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!

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

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.

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.

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.

Thanks a lot Danny.Your code helped me a lot!