I am trying to calculate a percentage within a subtotal that would use a value from that subtotal as well as from another subtotal, I am trying to get what percent of the total sales for each department, by class

Ex.

Department_____Class_____Sales
A______________1_________250.00
A______________1_________100.00
Subtotal of 1
percent of department 35%

A______________2_________50.00
A______________2_________25.00
A______________2_________25.00
Subtotal of 2
percent of department 10%


A______________3_________150.00
A______________3_________50.00
A______________3_________50.00
Subtotal of 3
percent of department 20%


A______________4_________100.00
A______________4_________200.00
Subtotal of 4
percent of department 30%

Subtotal of Department A
Total Sales

B______________1_________100.00
B______________1_________50.00
Etc...


How could i get those percentages? when I use the TOT perfix it give me the total of all the departments...


THanks for the help!

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

FILEDEF DATAMAST DISK DATA1.MAS
-RUN

-WRITE DATAMAST FILE=DATA1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=DEPT , ALIAS=E01, USAGE=A1    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=CLASS, ALIAS=E02, USAGE=A1    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=SALES, ALIAS=E03, USAGE=P10.2 , ACTUAL=A6, $

FILEDEF DATA1 DISK DATA1.FTM
-RUN

-WRITE DATA1 A1250.00
-WRITE DATA1 A1100.00
-WRITE DATA1 A2050.00
-WRITE DATA1 A2025.00
-WRITE DATA1 A2025.00
-WRITE DATA1 A3150.00
-WRITE DATA1 A3050.00
-WRITE DATA1 A3050.00
-WRITE DATA1 A4100.00
-WRITE DATA1 A4200.00
-WRITE DATA1 B1100.00
-WRITE DATA1 B1050.00
-RUN

TABLE FILE DATA1
SUM
SALES
BY DEPT
SUM
SALES

COMPUTE DEPT_PCT/P10 = C2 / C1 * 100; NOPRINT

BY DEPT
BY CLASS
PRINT
SALES
BY DEPT
BY CLASS

ON CLASS SUBFOOT
"Subtotal of <CLASS"
"Percent of department <DEPT_PCT|%"
END
-RUN

For an explanation of C1, C2... look for this documentation: Creating Reports With WebFOCUS Language > Creating Temporary Fields > Assigning Column Reference Numbers (Though the explanation is a bit vague...).

This message has been edited. Last edited by: Francis Mariani,

Thanks for the reply!

I reviewed the documentation and I don't quite understand C1, C2, Etc... Also is there a way of doing it without using them?

Thank you so much!

im Trying to do it with totCost and totSRP
under IN_COMCLASS


TABLE FILE DETAIL
SUM
totCost NOPRINT
totSRP NOPRINT
BY IN_DEPARTMENT NOPRINT
SUM
subtotcost NOPRINT
subtotSRP NOPRINT
subTotalProfit NOPRINT
BY IN_DEPARTMENT NOPRINT
BY IN_COMCLASS NOPRINT
SUM
IN_DEPARTMENT NOPRINT
AVE.IN_ITEMCODE/I7 AS 'ItemCode'
IN_COMCLASS AS 'CC'
IN_DESC AS 'Item Description'
AVE.IN_PACK AS 'Pack'
IN_SIZE AS 'Size'
IN_QTY AS 'Cases Del.'
IN_SELL_UNITS AS 'Time Del.'
UnitMove AS 'Unit Move'
totCost AS 'Total Amount Paid'
totSRP AS 'Total Sell Value'
AVE.PercProf AS '% Profit'
TotalProfit
COMPUTE PCTTOT/D9.2 = ( totCost / subtotcost ) * 100;
AS '% Sales @ Cost'
COMPUTE PCTSRP/D9.2 = ( totSRP / subtotSRP ) * 100; AS '% Sales @ Sell'
COMPUTE PCTPRO/D9.2 = ( TotalProfit / subTotalProfit ) * 100;
AS 'Dol Profit'
COMPUTE PCTBYDEP/D12.2 = C1-C2/C1;
BY IN_DEPARTMENT NOPRINT
BY IN_COMCLASS NOPRINT
BY TOTAL HIGHEST COMPUTE PCTPRO/D9.2 = ( TotalProfit / subTotalProfit ) * 100; NOPRINT
BY IN_ITEMCODE NOPRINT

ON IN_DEPARTMENT SUBTOTAL
TotalProfit
IN_QTY
totCost
totSRP AS '*TOTAL'

ON IN_COMCLASS SUBTOTAL
PCTBYDEP
TotalProfit
IN_QTY
totCost
totSRP AS '*TOTAL '
ON IN_COMCLASS RECAP
PercProf/D12.2 = ( totSRP - totCost ) * 100 / totSRP;

This message has been edited. Last edited by: Charles Richards,

You can avoid the column referencing by creating an intermediary HOLD file. (Don't forget to issue the SET ASNAMES command to make sure that total fields get named correctly). After creating the HOLD file, issue the ?FF command to see the fields in the HOLD file.

-SET &ECHO = 'ALL';

SET HOLDFORMAT = ALPHA
SET HOLDLIST   = PRINTONLY
SET ASNAMES    = ON
SET BYDISPLAY  = ON
-RUN

FILEDEF DATAMAST DISK DATA1.MAS
-RUN

-WRITE DATAMAST FILE=DATA1, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST   FIELD=DEPT , ALIAS=E01, USAGE=A1    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=CLASS, ALIAS=E02, USAGE=A1    , ACTUAL=A1, $
-WRITE DATAMAST   FIELD=SALES, ALIAS=E03, USAGE=P10.2 , ACTUAL=A6, $

FILEDEF DATA1 DISK DATA1.FTM
-RUN

-WRITE DATA1 A1250.00
-WRITE DATA1 A1100.00
-WRITE DATA1 A2050.00
-WRITE DATA1 A2025.00
-WRITE DATA1 A2025.00
-WRITE DATA1 A3150.00
-WRITE DATA1 A3050.00
-WRITE DATA1 A3050.00
-WRITE DATA1 A4100.00
-WRITE DATA1 A4200.00
-WRITE DATA1 B1100.00
-WRITE DATA1 B1050.00
-RUN

TABLE FILE DATA1
SUM
SALES     AS DEPT_SALES
BY DEPT

SUM
SALES     AS CLASS_SALES
BY DEPT
BY CLASS

PRINT
SALES
BY DEPT
BY CLASS
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

?FF H001
-RUN

TABLE FILE H001
PRINT
SALES
COMPUTE DEPT_PCT/P10 = CLASS_SALES / DEPT_SALES * 100; NOPRINT

BY DEPT
BY CLASS

ON CLASS SUBFOOT
"Subtotal of <CLASS"
"Percent of Department <DEPT_PCT|%"
END
-RUN