If this is a duplicate I apologize. I thought I submitted one this morning but can't find it.

I am trying to create percentages at 2 levels shop to dept, and dept to org. So I created the following multi verb step. What it is giving me is the counts are the same for the org and the dept; for example TOT_ORG_CREATED IS 1354 AND TOT_DEPT_CREATED IS ALSO 1354. I expected TOT_ORG to be 1354 but TOT_DEPT should be 207. I can get that if I comment out the first SUM and COMPUTEs. What do I need to do to get these 2 totals so that they can be used in the calculation?

The second part is the user has a spreadsheet that we are trying to mimic that has a formula to calculate the dpet to org percent in a cell that is in the same column as the shop to dept percent. I don't think I can do that can I? It is going to have ot be a new column, right?
For example:

3 columns

Shop Count Percent
---- ----- ---------------------
shop1 10 5 (10/200 Dept total)
shop2 25 12.5 (25/200)
Dept1 35 7.77 (35/450 org total)

 
SET ASNAMES=ON
TABLE FILE HOLD
PRINT *
BY DEPT
ON TABLE HOLD AS HOLD2
END
-*-EXIT
DEFINE FILE HOLD2
FLDA/A1 = ' ';
END
TABLE FILE HOLD2
SUM
COMPUTE TOT_ORG_OPEN/D10 = OPEN_COUNT;
COMPUTE TOT_ORG_CREATED/D10 = CREATED_COUNT;
COMPUTE TOT_ORG_CANCELED/D10 = CANCELED_COUNT;
COMPUTE TOT_ORG_CLOSED/D10 = CLOSED_COUNT;

SUM
COMPUTE TOT_DEPT_OPEN/D10 = OPEN_COUNT;
COMPUTE TOT_DEPT_CREATED/D10 = CREATED_COUNT;
COMPUTE TOT_DEPT_CANCELED/D10 = CANCELED_COUNT;
COMPUTE TOT_DEPT_CLOSED/D10 = CLOSED_COUNT;
BY DEPT
PRINT
SHOP OPEN_COUNT CREATED_COUNT
COMPUTE
SHOP_PCT_CREATED/D12.2% = (CREATED_COUNT/TOT_DEPT_CREATED)*100;
DEPT_PCT_CREATED/D12.2% = (TOT_DEPT_CREATED/TOT_ORG_CREATED)*100;
CANCELED_COUNT CLOSED_COUNT
BY DEPT
BY SHOP
ON TABLE SUBTOTAL
END 

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

you should create fields in a define and use them in the multiverb

in the percentage computation refer to the column instead to the fieldnames

so:

DEFINE FILE HOLD2
TOTOPEN=OPEN_COUNT;

etc
SUBTOTOPEN=OPEN_COUNT;
END
TABLE FILE HOLD2
SUM
TOTOPEN
SUM
SUBTOTOPEN
BY DEPT
PRINT
SHOP OPEN_COUNT CREATED_COUNT
COMPUTE
SHOP_PCT_CREATED/D12.2% = (CREATED_COUNT/C1)*100;
DEPT_PCT_CREATED/D12.2% = (TOT_DEPT_CREATED/C2)*100;
BY DEPT
BY SHOP
ON TABLE SUBTOTAL
END 

you have to play with the column numbers to find the proper column

Not actually seeing what you are getting I can't say for sure, but good old fashion column calculations will work. (Yes guys I know about AVE. but I manually calculated an average just to show that this works):
TABLE FILE EMPLOYEE
SUM
CURR_SAL AS 'Total,Compay,Salary'
CNT.CURR_SAL AS 'Company,# Emps'
COMPUTE COMPANYAVESAL/P9.2 = C1 / C2; AS 'Compay,Ave Salary'
-*
SUM
CURR_SAL AS 'Dept ,Salary,Total'
CNT.CURR_SAL AS '# Dept,Emps'
COMPUTE DEPTAVESAL/P9.2 = C4 / C5; AS 'Dept Ave,Salary'
COMPUTE DEPTPCTOFTOTALSAL/P8.2% = ( C4 / C1 ) * 100; AS 'Dept % of,Total Salary'
COMPUTE DEPTPCTOFTOTALEMPS/P8.2% = ( C5 / C2 ) * 100; AS 'Dept % of,Total Emps'
BY DEPARTMENT AS 'Dept'
-*
SUM
CURR_SAL AS 'Total Dept,JobCode,Salary'
CNT.CURR_SAL AS 'JobCode/,Department,# Emps'
COMPUTE JCAVESAL/P9.2 = C9 / C10; AS 'JobCode,Ave Salary'
COMPUTE JCPCTOFTOTALSAL/P8.2% = ( C9 / C1 ) * 100; AS 'JobCode,% of total,Salary'
COMPUTE JCPCTOFTOTALEMPS/P8.2% = ( C10 / C2 ) * 100; AS 'JobCode,% of total,Emps'
COMPUTE JCPCTOFDEPTSAL/P8.2% = ( C9 / C4 ) * 100; AS 'JobCode,% of Dept ,Salary'
COMPUTE JCPCTOFDEPTEMP/P8.2% = ( C10 / C5 ) * 100; AS 'JobCode,% of Dept,Emps'
BY DEPARTMENT AS 'Dept'
BY CURR_JOBCODE AS 'JobCode'
END

Bethanne, a very nice example. For reasons of decipherability and easier maintenance in the future, I prefer to use column names instead of column notation references. To wit, I took your example and, using Frank's suggestion to use DEFINEs, modified it to eliminate the column notation references.

DEFINE FILE EMPLOYEE
 TOT_COMP_SAL/D12.2M = CURR_SAL ;
 TOT_COMP_EMP/I5 = 1 ;
 TOT_DEPT_SAL/D12.2M = CURR_SAL ;
 TOT_DEPT_EMP/I5 = 1 ;
 TOT_JOB_SAL/D12.2M = CURR_SAL ;
 TOT_JOB_EMP/I5 = 1 ;
END

TABLE FILE EMPLOYEE
 SUM
  TOT_COMP_SAL AS 'Total Company Salary'
  TOT_COMP_EMP AS 'Company # Emps'
  COMPUTE COMPANYAVESAL/D12.2M = TOT_COMP_SAL / TOT_COMP_EMP ; AS 'Company Ave Salary'
-*
 SUM
  TOT_DEPT_SAL AS 'Dept Salary Total'
  TOT_DEPT_EMP AS '# Dept Emps'
  COMPUTE DEPTAVESAL/D12.2M = TOT_DEPT_SAL / TOT_DEPT_EMP ; AS 'Dept Ave Salary'
  COMPUTE DEPTPCTOFTOTALSAL/P8.2% = ( TOT_DEPT_SAL / TOT_COMP_SAL ) * 100 ; AS 'Dept % of Total Salary'
  COMPUTE DEPTPCTOFTOTALEMPS/P8.2% = ( TOT_DEPT_EMP / TOT_COMP_EMP ) * 100 ; AS 'Dept % of Total Emps'
 BY DEPARTMENT AS 'Dept'
-*
 SUM
  TOT_JOB_SAL AS 'Total Dept JobCode Salary'
  TOT_JOB_EMP AS 'JobCode/ Department # Emps'
  COMPUTE JCAVESAL/D12.2M = TOT_JOB_SAL / TOT_JOB_EMP ; AS 'JobCode Ave Salary'
  COMPUTE JCPCTOFTOTALSAL/P8.2% = ( TOT_JOB_SAL / TOT_COMP_SAL ) * 100 ; AS 'JobCode % of total Salary'
  COMPUTE JCPCTOFTOTALEMPS/P8.2% = ( TOT_JOB_EMP / TOT_COMP_EMP ) * 100 ; AS 'JobCode % of total Emps'
  COMPUTE JCPCTOFDEPTSAL/P8.2% = ( TOT_JOB_SAL / TOT_DEPT_SAL ) * 100 ; AS 'JobCode % of Dept Salary'
  COMPUTE JCPCTOFDEPTEMP/P8.2% = ( TOT_JOB_EMP / TOT_DEPT_EMP ) * 100 ; AS 'JobCode % of Dept Emps'
 BY DEPARTMENT AS 'Dept'
 BY CURR_JOBCODE AS 'JobCode'
 ON TABLE RECOMPUTE
END

Great that's what I needed, especially being able to get away from column notation.

If I change the ON phrase to give me dept subtotals the first dept MIS gives me a 0 tot_comp_sal value; therfore the dept total calculation ends up as 0% and that's not right.

The second dept Production is OK.
Any ideas?

 
DEFINE FILE EMPLOYEE
 TOT_COMP_SAL/D12.2M = CURR_SAL ;
 TOT_COMP_EMP/I5 = 1 ;
 TOT_DEPT_SAL/D12.2M = CURR_SAL ;
 TOT_DEPT_EMP/I5 = 1 ;
 TOT_JOB_SAL/D12.2M = CURR_SAL ;
 TOT_JOB_EMP/I5 = 1 ;
END

TABLE FILE EMPLOYEE
 SUM
  TOT_COMP_SAL AS 'Total Company Salary'
  TOT_COMP_EMP AS 'Company # Emps'
  COMPUTE COMPANYAVESAL/D12.2M = TOT_COMP_SAL / TOT_COMP_EMP ; AS 'Company Ave Salary'
-*
 SUM
  TOT_DEPT_SAL AS 'Dept Salary Total'
  TOT_DEPT_EMP AS '# Dept Emps'
  COMPUTE DEPTAVESAL/D12.2M = TOT_DEPT_SAL / TOT_DEPT_EMP ; AS 'Dept Ave Salary'
  COMPUTE DEPTPCTOFTOTALSAL/P8.2% = ( TOT_DEPT_SAL / TOT_COMP_SAL ) * 100 ; AS 'Dept % of Total Salary'
  COMPUTE DEPTPCTOFTOTALEMPS/P8.2% = ( TOT_DEPT_EMP / TOT_COMP_EMP ) * 100 ; AS 'Dept % of Total Emps'
 BY DEPARTMENT AS 'Dept'
-*
 SUM
  TOT_JOB_SAL AS 'Total Dept JobCode Salary'
  TOT_JOB_EMP AS 'JobCode/ Department # Emps'
  COMPUTE JCAVESAL/D12.2M = TOT_JOB_SAL / TOT_JOB_EMP ; AS 'JobCode Ave Salary'
  COMPUTE JCPCTOFTOTALSAL/P8.2% = ( TOT_JOB_SAL / TOT_COMP_SAL ) * 100 ; AS 'JobCode % of total Salary'
  COMPUTE JCPCTOFTOTALEMPS/P8.2% = ( TOT_JOB_EMP / TOT_COMP_EMP ) * 100 ; AS 'JobCode % of total Emps'
  COMPUTE JCPCTOFDEPTSAL/P8.2% = ( TOT_JOB_SAL / TOT_DEPT_SAL ) * 100 ; AS 'JobCode % of Dept Salary'
  COMPUTE JCPCTOFDEPTEMP/P8.2% = ( TOT_JOB_EMP / TOT_DEPT_EMP ) * 100 ; AS 'JobCode % of Dept Emps'
 BY DEPARTMENT AS 'Dept'
 BY CURR_JOBCODE AS 'JobCode'
 ON DEPARTMENT RECOMPUTE
END
 

Rick,

This looks to me as though it's a bug. Using SUBTOTAL (or SUB-TOTAL) the 0% are calculated but are (obviously) out due to the summation and no recalculation, so it looks like a RECOMPUTE and SUMMARIZE problem.

You should kick this to Tech Support, include your sample code as it uses IB sample data.

T

I've opened a case with IBI and they are working on it.

Part of the soultion is to use WITHIN to accumalate, and COLSPAN to position the Dept to ORG percent. However, there is still a column total issue.
When we get it all figured out I'll update and post the code.

Here is the current state:

 
DEFINE FILE EMPLOYEE
TOT_COMP_SAL/D12.2M = CURR_SAL ;
TOT_COMP_EMP/I5 = 1 ;

TOT_DEPT_SAL/D12.2M = CURR_SAL ;
TOT_DEPT_EMP/I5 = 1 ;

TOT_JOB_SAL/D12.2M = CURR_SAL ;
TOT_JOB_EMP/I5 = 1 ;
END

TABLE FILE EMPLOYEE
SUM
TOT_COMP_SAL  WITHIN TABLE
TOT_COMP_EMP  WITHIN TABLE
COMPUTE COMPANYAVESAL/D12.2M = TOT_COMP_SAL / TOT_COMP_EMP ; AS 'Company Ave Salary'
-*
-* SUM
TOT_DEPT_SAL WITHIN DEPARTMENT
TOT_DEPT_EMP WITHIN DEPARTMENT
COMPUTE DEPTAVESAL/D12.2M = TOT_DEPT_SAL / TOT_DEPT_EMP ; AS 'Dept Ave Salary'
COMPUTE DEPTPCTOFTOTALSAL/P8.2% = ( TOT_DEPT_SAL / TOT_COMP_SAL ) * 100 ; AS
'Dept % of Total Salary'
COMPUTE DEPTPCTOFTOTALEMPS/P8.2% = ( TOT_DEPT_EMP / TOT_COMP_EMP ) * 100 ; AS
'Dept % of Total Emps'
-* BY DEPARTMENT AS 'Dept'
-*
-* SUM
TOT_JOB_SAL
TOT_JOB_EMP
COMPUTE JCAVESAL/D12.2M = TOT_JOB_SAL / TOT_JOB_EMP ; AS 'JobCode Ave Salary'
COMPUTE JCPCTOFTOTALSAL/P8.2% = ( TOT_JOB_SAL / TOT_COMP_SAL ) * 100 ; AS
'JobCode % of total Salary'
COMPUTE JCPCTOFTOTALEMPS/P8.2% = ( TOT_JOB_EMP / TOT_COMP_EMP ) * 100 ; AS
'JobCode % of total Emps' NOPRINT
COMPUTE JCPCTOFDEPTSAL/P8.2% = ( TOT_JOB_SAL / TOT_DEPT_SAL ) * 100 ; AS
'JobCode % of Dept Salary'
COMPUTE JCPCTOFDEPTEMP/P8.2% = ( TOT_JOB_EMP / TOT_DEPT_EMP ) * 100 ; AS
'JobCode % of Dept Emps'
BY DEPARTMENT AS 'Dept'
BY CURR_JOBCODE AS 'JobCode'
WHERE CURR_JOBCODE NE 'A15'
ON DEPARTMENT SUBFOOT
"Percent Org<JCPCTOFTOTALEMPS<JCPCTOFDEPTEMP"
ON DEPARTMENT RECOMPUTE
ON TABLE PCHOLD FORMAT EXL2K
HEADING
"W/RECOMPUTE"
ON TABLE SET STYLE *
TYPE=SUBFOOT, HEADALIGN=BODY,$
TYPE=SUBFOOT,LINE=1,ITEM=1,COLSPAN=13, $
TYPE=SUBFOOT,LINE=1,ITEM=2,COLSPAN=1,$
TYPE=SUBFOOT,LINE=1,ITEM=3,COLSPAN=2,$
ENDSTYLE
END

 

We figured it out. We needed to do a WITHIN, a couple of SUBFOOTs and some STYLEing. See the code for more comments. Here is a picture of the output. The highlighed items were the problem children. That darn wrapping issue bit me again grrrrr.

Angie and Caesar from tech support were great.

http://tinypic.com/r/2d9ao8/7

 
SET ASNAMES=ON
-*
-STEP1
DEFINE FILE PHYSPLANT
TOT_ORG_OPEN/D10     = OPEN_COUNT;
TOT_ORG_CREATED/D10  = CREATED_COUNT;
TOT_ORG_CANCELED/D10 = CANCELED_COUNT;
TOT_ORG_CLOSED/D10   = CLOSED_COUNT;
-*
TOT_DEPT_OPEN/D10     = OPEN_COUNT;
TOT_DEPT_CREATED/D10  = CREATED_COUNT;
TOT_DEPT_CANCELED/D10 = CANCELED_COUNT;
TOT_DEPT_CLOSED/D10   = CLOSED_COUNT;
-*
TOT_SHOP_OPEN/D10     = OPEN_COUNT;
TOT_SHOP_CREATED/D10  = CREATED_COUNT;
TOT_SHOP_CANCELED/D10 = CANCELED_COUNT;
TOT_SHOP_CLOSED/D10   = CLOSED_COUNT;
FLDA/A1 = '';
END
TABLE FILE PHYSPLANT
HEADING
"FSU PHYSICAL PLANT SCORECARD - BY DEPARTMENT"
SUM
-* Count number of shops touse in TOTPERC calculation
CNT.SHOP NOPRINT
-* accumulate totals for the ORG
TOT_ORG_OPEN     WITHIN TABLE NOPRINT
TOT_ORG_CREATED  WITHIN TABLE NOPRINT
TOT_ORG_CANCELED WITHIN TABLE NOPRINT
TOT_ORG_CLOSED   WITHIN TABLE NOPRINT
-* accumulate totals for the DEPT
TOT_DEPT_OPEN     WITHIN DEPT NOPRINT
TOT_DEPT_CREATED  WITHIN DEPT NOPRINT
TOT_DEPT_CANCELED WITHIN DEPT NOPRINT
TOT_DEPT_CLOSED   WITHIN DEPT NOPRINT
-*
OPEN_COUNT    AS '# OF WORK,ORDER PHASES,OPEN'
CREATED_COUNT AS '# OF WORK,ORDER PHASES,CREATED'
-* calculate shop to dept percent
COMPUTE SHOPTODEPT_PCT_CREATED/D12.2% = (CREATED_COUNT/TOT_DEPT_CREATED)*100; AS '%OF TOTAL,CREATED WORK'
-* calculate shop to org percent
COMPUTE SHOPTOORG_PCT_CREATED/D12.2%  = (TOT_SHOP_CREATED/TOT_ORG_CREATED)*100; NOPRINT
-* calculate dept to org percent
COMPUTE DEPTTOORG_PCT_CREATED/D12.2%  = (TOT_DEPT_CREATED/TOT_ORG_CREATED)*100; NOPRINT
CANCELED_COUNT AS '# OF WORK,ORRDER PHASES,CANCELLED'
CLOSED_COUNT   AS '# OF WORK,ORDER PHASES,CLOSED'
BY DEPT
BY SHOP
-* create SUBFOOT for totals by dept
ON DEPT SUBFOOT
"<DEPT TOTALS <ST.OPEN_COUNT<ST.CREATED_COUNT<ST.SHOPTODEPT_PCT_CREATED"
"<DEPT PERCENT TO ORG:<DEPTTOORG_PCT_CREATED"
" "
-* Need to calculate total org percent, otherwise need to use a multi verb and
-*  that causes the total line to wrap
ON TABLE RECAP TOTPERC/D12.2% = ((TOT_ORG_CREATED/CNT.SHOP)/CREATED_COUNT) * 100;
-* create SUBFOOT for totals by org
ON TABLE SUBFOOT
"OVERALL PHYSICAL PLANT DEPARTMENT <ST.OPEN_COUNT<ST.CREATED_COUNT<TOTPERC"
ON TABLE SET STYLE *
TYPE=SUBFOOT, HEADALIGN=BODY,$
-* position the 'fileds/items' in the subfoot in the right columns
TYPE=SUBFOOT, LINE=1,ITEM=3,COLSPAN=1,$
TYPE=SUBFOOT, LINE=2,ITEM=3,COLSPAN=3,$
TYPE=TABFOOTING, HEADALIGN=BODY,$
-* Position the 'fields/items' in the total line in the right columns
TYPE=TABFOOTING, LINE=1, ITEM=1, COLSPAN=2,$
TYPE=TABFOOTING, LINE=1, ITEM=4, JUSTIFY=RIGHT,$
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END