My ACROSS and RECOMPUTE commands are not working as expected. I am getting totals for CALC_AUM and CALC_T12_PROD just added up for both genders for the ACROSS with COMPUTE. I need the CALC_AUM and CALC_T12_PROD computed fields to be re-averaged for the totals in the ACROSS. Any ideas from anyone?

Also, I need to recompute the average age for the totals with this logic. Now, I have a DEFINE field using the DATEDIF and then summing the average of that field. Any ideas for recomputing that for the totals here also?

DEFINE FILE IBI_FA_STATS
Gender_Translation/A25=IF IBI_FA_STATS.IBI_FA_STATS.GENDER EQ 'F' THEN 'Female' ELSE IF IBI_FA_STATS.IBI_FA_STATS.GENDER EQ 'M' THEN 'Male' ELSE 'Other' ;
Age/I5=DATEDIF ( &SF_TODAY_YYMD , IBI_FA_STATS.IBI_FA_STATS.DOB , 'Year' ) ;
END

TABLE FILE IBI_FA_STATS
SUM CNT.DST.IBI_FA_STATS.IBI_FA_STATS.EMPLOYEE_NUMBER AS '# Advisors'
OVER AVE.Age AS 'Average Age'
-*OVER COMPUTE Age = DATEDIF ( &SF_TODAY_YYMD , IBI_FA_STATS.IBI_FA_STATS.DOB , 'Year' ) ;
OVER IBI_FA_STATS.IBI_FA_STATS.AUM/P33.2M
-*OVER AVE.IBI_FA_STATS.IBI_FA_STATS.AUM/P33.2M AS 'Average AUM'
OVER COMPUTE CALC_AUM/P33.2M = AVE.AUM; AS 'Average AUM';
OVER IBI_FA_STATS.IBI_FA_STATS.T12_PRODUCTION/P14.2M AS 'T12 Production'
-*OVER AVE.IBI_FA_STATS.IBI_FA_STATS.T12_PRODUCTION/P14.2M AS 'Average T12'
OVER COMPUTE CALC_T12_PROD/P14.2 = AVE.T12_PRODUCTION; AS 'Average T12'
OVER IBI_FA_STATS.IBI_FA_STATS.CHAIRMAN_IND AS 'Chariman''s Level'
OVER IBI_FA_STATS.IBI_FA_STATS.PRESIDENT_IND AS 'President''s Level'
ACROSS Gender_Translation SUB-TOTAL NOPRINT AND RECOMPUTE CALC_AUM; RECOMPUTE CALC_T12_PROD;

This message has been edited. Last edited by: FP Mod Chuck,

quote:

computed fields to be re-averaged for the totals

Please try to calculate average with the standard formula with a COMPUTE statement not using by WebFOCUS aggregation function(AVE)-then the RECOMPUTE will work

 TABLE FILE CAR
SUM
AVE.SALES AS 'AVESALES BY AVE.'  OVER
COMPUTE AVESALES/I4 = SALES / CNT.MODEL; AS 'AVESALES BY COMPUTE' OVER
SALES  
ACROSS  CAR
 ON CAR RECOMPUTE 
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END 

This message has been edited. Last edited by: Chaudhary,

Chaudhary - Thanks for seeing that and steering me in that direction. That worked. I also used the same concept for the average age calculation and it took care of that as well. Thank you.

Chaudhary - Also, need to calculate percentage of females and percentage of males as a ratio to the overall total of advisors. I am trying something like this below, but not getting it to come out correctly. I can get the count of total employees, but the number of females and males I'm having the issue with at this point in the calculation.



OVER COMPUTE PERCTOT/I3 = CNT.CNT_TOT / CNT.EMPLOYEE_NUMBER; AS '% of Advisors';

Chaudary - I'm also looking at the RPCT command as well since I an using an ACROSS value. Let me know your thoughts if possible.

Hi AMC,

Are you looking something like this ?

 TABLE FILE CAR
SUM 
     CNT.CAR.CARREC.MODEL AS 'MODELCNT' 
CNT.CAR.CARREC.MODEL WITHIN TABLE AS 'TOTCNT'
BY  CAR.COMP.CAR
BY COUNTRY
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS  CNT_TOCNT
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
TABLE FILE CNT_TOCNT
SUM 
MODELCNT
TOTCNT
COMPUTE PCT/D12.2% = MODELCNT/TOTCNT ;  AS '%'
BY CAR
ACROSS  COUNTRY
END

FOR RPCT please refer this link RPCT IN WebFOCUS

This message has been edited. Last edited by: Chaudhary,

Hi Chaudhary - No, I'm not holding my data first. None of my other metrics require me to sum and hold first. However, I may need to do that first for the percentage calculation with the across. Is that what you're getting at?

Chaudhary - This is what I have now below. Trying to compute the percentage for female % of advisors referenced by the COMPUTE F_PERCTOT field. I am trying to see how this would fit into your example you posted with creating a hold file first. I have other calculated metrics with the ACROSS field.

   
DEFINE FILE IBI_FA_STATS
Gender_Desc/A25=IF IBI_FA_STATS.IBI_FA_STATS.GENDER EQ 'F' THEN 'Female' ELSE IF IBI_FA_STATS.IBI_FA_STATS.GENDER EQ 'M' THEN 'Male' ELSE 'Other' ;
Age/I5=DATEDIF ( &SF_TODAY_YYMD , IBI_FA_STATS.IBI_FA_STATS.DOB , 'Year' ) ;
CNT_TOT/I3 = IF GENDER EQ 'F' OR 'M' THEN 1 ELSE 0;
FEMALE_CNT/I5 = IF GENDER EQ 'F' THEN 1 ELSE 0;
MALE_CNT/I5 = IF GENDER EQ 'M' THEN 1 ELSE 0;
D_METRIC/A10 = 'Metric';
D_FEMALE/A10 = 'Female';
D_MALE/A10 = 'Male';
D_FIRM/A10 = 'Firm';
D_DUM/A10 = ' ';
END
-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';

TABLE FILE IBI_FA_STATS
HEADING
"<+0> Advisor Demographics </1"
"Metric <+0> Female <+0> Male <+0> Firm"
-*"<D_METRIC <D_FEMALE <D_MALE <D_FIRM"
SUM CNT.DST.IBI_FA_STATS.IBI_FA_STATS.EMPLOYEE_NUMBER AS '# Advisors'
-*OVER PCT.CNT.EMPLOYEE_NUMBER AS '% of Advisors'
 [COLOR:RED]OVER COMPUTE F_PERCTOT/D12.2% = FEMALE_CNT / CNT.EMPLOYEE_NUMBER * 100; AS 'Female % of Advisors';[/COLOR] OVER COMPUTE F_PERCTOT/D12.2% = FEMALE_CNT / CNT.EMPLOYEE_NUMBER * 100; AS 'Female % of Advisors';
-*COMPUTE M_PERCTOT/I3 = MALE_CNT / CNT.EMPLOYEE_NUMBER; AS 'Male % of Advisors';
-*OVER AVE.Age AS 'Average Age'
-*OVER COMPUTE Age = DATEDIF ( &SF_TODAY_YYMD , IBI_FA_STATS.IBI_FA_STATS.DOB , 'Year' ) ;
OVER COMPUTE AVG_AGE/I5 = Age / CNT.EMPLOYEE_NUMBER; AS 'Average Age';
OVER IBI_FA_STATS.IBI_FA_STATS.AUM/P33.2M
-*OVER RPCT.AUM AS '% of AUM'
-*OVER AVE.IBI_FA_STATS.IBI_FA_STATS.AUM/P33.2M AS 'Average AUM'
OVER COMPUTE CALC_AUM/P33.2M = AUM / CNT.EMPLOYEE_NUMBER; AS 'Average AUM';
OVER IBI_FA_STATS.IBI_FA_STATS.T12_PRODUCTION/P14.2M AS 'T12 Production'
-*OVER RPCT.T12_PRODUCTION AS '% of T12 Production'
-*OVER AVE.IBI_FA_STATS.IBI_FA_STATS.T12_PRODUCTION/P14.2M AS 'Average T12'
OVER COMPUTE CALC_T12_PROD/P14.2M = T12_PRODUCTION / CNT.EMPLOYEE_NUMBER; AS 'Average T12';
OVER IBI_FA_STATS.IBI_FA_STATS.CHAIRMAN_IND AS 'Chariman''s Level'
OVER IBI_FA_STATS.IBI_FA_STATS.PRESIDENT_IND AS 'President''s Level'
ACROSS Gender_Desc NOPRINT AND RECOMPUTE CALC_AUM; RECOMPUTE CALC_T12_PROD; RECOMPUTE F_PERCTOT